Reply
jwhk
Helper III
Helper III

Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

Dear Microsoft Power Automate Community,

 

Refer to the attached screenshot, I have created a flow to run the recurrence on three (3) Scopes that will run DAX queries on the same Power BI dataset. However, I have the following queries for the next steps:

 

1) How to combine two (2) DAX queries into one (1) single CSV table? Should I use "first table rows" or "output" from Create CSV Table? Or should I use Compose/Select then use "Union" function to join them?

 

2) With the generated CSV tables, how to combine these CSV tables into three (3) different Worksheets in one (1) single Excel file?

 

I have tried to google-ed the answers from this forum (such as the link below), but I still can't understand how to do it yet.

 

Combine Multiple CSV tables into one file - Power Platform Community (microsoft.com)

 

Much appreciated for your advise.

73 REPLIES 73
v-yueyun-msft
Community Support
Community Support

Hi , @jwhk 

According to your description, you want to combine array in the flow. 

So you need to first make the Object has the same field like this:

vyueyunmsft_0-1707112304116.png

Then you can use the union() function to combine two arrays:

union(outputs('Compose'),outputs('Compose_2'))
 
And you can use the "Select" action to make the Object has the same field:
vyueyunmsft_1-1707112575361.png

 

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

 

Best Regards,

Yueyun Zhang

 

 
jwhk
Helper III
Helper III

Hi @v-yueyun-msft ,

 

Thanks for your reply.

 

Your answer is for combining 2 DAX queries (first table rows) into 1 CSV table? Or combining 3 CSV tables into 3 different Worksheets in 1 Excel file?

 

You meant I can use "Compose" action or "Select" action to make the Object has the same field? I still don't understand how to identify the same fields from the DAX queries stated in "Query Text" of "Run A Query Against A Dataset" action.  I attached the 2 DAX queries I abstracted from "Run A Query Against A Dataset 1a" and "Run A Query Against A Dataset 1b" actions (as shown in my previous screenshot).

 

If you refer to the screenshot I shared earlier, could you show me how the flow should be looked like? Is it putting the "Select" action after the two "Run A Query Against A Dataset" actions? Or the two "Run A Query Against A Dataset" actions should run in parallel and follow by "Select" action under each "Run A Query Against A Dataset" action?

HI , @jwhk 

Thanks for your quick response !  In my understand , you want to combine the data output from your dataset and export to one Excel.

So the column in your Excel should be static and the same column Name. Then before the merge, you need to unify all the return lists of Run a query against a dataset" action into the same FieldName(which we can use the "Select" action).

vyueyunmsft_0-1707126019010.png

Then all the array with the same fieldName , we can use the union() function to combine all the data in an array, like my first reply (These two array have the same fieldsName "Name" and "Age"):

vyueyunmsft_1-1707126182034.png

And the Select function is used to unify the fieldName :

vyueyunmsft_2-1707126414484.png

 

 

 

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

 

Best Regards,

Yueyun Zhang

@jwhk 

For step 2, Encodian's Merge Excel Files action will take different Excel or CSV files and merge them into a Excel or CSV single file containing all the worksheets.

Hi @v-yueyun-msft,

 

Sorry that my earlier explanations were not clear. Refer to the screenshot below, the Power Report has 2 tables next to each other. After performed two "Run A Query Against A Dataset" actions, I would like to put these 2 tables (or DAX queries) next to each other in one single CSV file.

 

Is that possible? If yes, could you explain to me the actions that need to be performed before and after the "Run A Query Against A Dataset" actions? Thank you!

 

jwhk_0-1707170446547.png

 

Hi , @jwhk 

Thanks for your quick response! Do you mean you want to make the csv data(different column name) in one csv file.

There is no directly action flow to combine the csv data now. As you can refer to my test flow if it can meet your need:

vyueyunmsft_0-1707185119120.png

concat(body('Create_CSV_table'),decodeUriComponent('%0D%0A%0D%0A'),body('Create_CSV_table_2'))

 

The result is as follows:

vyueyunmsft_1-1707185148283.png

 

 

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

 

Best Regards,

Yueyun Zhang

 

Hi @v-yueyun-msft,

 

Thanks for your prompt reply. Let me try it out from my end.

Hi , @jwhk 

Thanks for your response!

In "Create csv table" action, i use the "First table rows".

vyueyunmsft_0-1707190413457.png

As you need to first initial variable before "append to string variable".

vyueyunmsft_1-1707190476719.png

concat(body('Create_CSV_table'),decodeUriComponent('%0D%0A%0D%0A'),body('Create_CSV_table_2'))

 

The decodeUriComponent('%0D%0A%0D%0A') is \r\n to add the line breaks in csv file.

 

 

Best Regards,

Yueyun Zhang

 

 

Hi @v-yueyun-msft,

 

Thanks for your prompt response again.

 

Just now I realised the name in "Append to string variable" action will not work without putting "Initialize variable" action before "Run a query against a dataset" action.

 

I also noticed decodeUriComponent('%0D%0A%0D%0A') will add the line breaks in between the 2 tables in CSV file. Is it possible to put the 2 tables next to each other instead of one at the top and one at the bottom?

 

The next action after "Append to string variable" will be "Create File" action?

Hi @v-yueyun-msft,

 

Refer to the screenshot below, what you guided me just now was working well, except "Create file 6" and "Create file 10" where the CSV files couldn't be opened with "Sorry, something went wrong." error (see the screenshot below).

jwhk_1-1707195525618.png 

 

jwhk_2-1707195632332.png

Also, some CSV files contained the "IsGrandTotalRowTotal" and "IsDM0Total" columns with FALSE and TRUE values. How can I remove these 2 columns automatically?

jwhk_3-1707195797529.png

 

Hi , @jwhk 

It is complex to put the two csv left and right.

And you want to remove  "IsGrandTotalRowTotal" and "IsDM0Total" columns , you need to use the Custom in "Create csv table":

vyueyunmsft_0-1707196066115.png

 item()?['fieldName']

 

Best Regards,

Yueyun Zhang

Hi @v-yueyun-msft ,

 

How complex to put the two csv left and right? I prefer to be left and right because sometimes the data in first table/csv can be very long and the viewer has to keep on scrolling down to view the second table/csv.

 

How this Custom columns work in "Create CSV table" action? I can enter any value for "Header" parameter? In "Value" parameter, I couldn't find item() function in the list. Can I just manually type item()? and following by the column names I set in DAX query?

 

Do you know why I encountered "Sorry, something went wrong." error for the csv files created under "Create file 6" and "Create file 10" actions?

Hi , @jwhk 

First , if you want to put two csv left and right. We need to determine the number of rows in two csv. Then merge the two csv files with the maximum number of rows. Then blank lines need to use comma placeholders.

Sure , You can custom the column name in the Custom "Create CSV table" action.

Yes , we need to use the expression in the Custom mode.

vyueyunmsft_0-1707199407232.png

The feildName you can find in the flow run history, Like below is :
Table 2[Column]   ===》     item()?['Table 2[Column]']

vyueyunmsft_1-1707199451624.png

For your error code, can you show the run flow history for your "Create csv file" action and the configuration of your "Create file" action?

 

Best Regards,

Yueyun Zhang

Hi @v-yueyun-msft,

 

Thanks for your replies yesterday.

 

For "Sorry, something went wrong." error occurred when opening the csv file, this morning I did the test run again. This time I noticed only "Create file 10" action has this error, where it created a blank csv file due to no data displayed in the Power BI report. The "Create file 4" action also created a blank csv file but didn't encounter this error. If I compared both "Create file 10" action and "Create file 4" action, I saw "Create file 10" action has the Size value of 0 and "Create file 4" action has Size value of 4.

 

jwhk_0-1707264341854.png

 

For my question about Custom columns in "Create CSV table" action, if you look at the attached .docx file, can you guide me which one is the fieldName I should put in custom column value?

 

For putting two csv left and right, the number of rows can change from time to time. Can we set the allowed maximum rows? Can you show me how to run this on "Append to string variable" action?

Hi , @jwhk 

Thanks for your response! For the custom column value , you can see here in your run flow history:

vyueyunmsft_0-1707272101118.png

And for your "Something went wrong", can you check if has the value return in your "Run a query against a dataset"?

Best Regards,

Yueyun Zhang

 

Hi @v-yueyun-msft,

 

For the custom column value , I received different Outputs for different "Run a query against a dataset" actions. Some of them only showing "[ ]" and some showed proper fieldNames like your screenshot. What is "[ ]"?

jwhk_1-1707274608035.png

 

For "Sorry, something went wrong." error, you can see the Outputs of First table rows is showing "[ ]". Both "Run a Query against a dataset" actions have the same Outputs of "[ ]".

 

jwhk_0-1707274181237.png

 

Hi, @jwhk 

Thanks for your response! The '[]' means the blank array which means using your Dax query , it return blank from Power BI dataset.

 

Best Regards,

Yueyun Zhang

Hi @v-yueyun-msft,

 

Thanks for your answer.

 

So I can't configure the custom column value until the DAX query returned value/outputs?

 

For "Sorry, something went wrong." error, do you know why some blank csv files could be opened without error but not the blank csv file created under "Create file 10" action?

 

Also, could you guide me how to put two csv  left and right?

Hi , @jwhk 

For the custom column need to configure after the "run a query against a dataset" action. And the best way to see the column name is in the run flow history. But it can also see it in your Dax, Like this:

vyueyunmsft_0-1707284546986.png

 

For your second error , can you add the condition if the array is blank then it will not create this file?

vyueyunmsft_1-1707285317481.png

length(outputs('Run_a_query_against_a_dataset')?['body/firstTableRows'])

 

Best Regards,

Yueyun Zhang

avatar user

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (1,325)