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

[jwhk]

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.

[v-yueyun-msft]

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:

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:

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]

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?

[v-yueyun-msft]

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).

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"):

And the Select function is used to unify the fieldName :

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

[AlexEncodian]

@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.

[jwhk]

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!

[v-yueyun-msft]

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:

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

The result is as follows:

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]

Hi @v-yueyun-msft,

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

[v-yueyun-msft]

Hi , @jwhk 

Thanks for your response!

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

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

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

[jwhk]

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?

[jwhk]

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).

 

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

[v-yueyun-msft]

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":

 item()?['fieldName']

Best Regards,

Yueyun Zhang

[jwhk]

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?

[v-yueyun-msft]

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.

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

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

[jwhk]

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.

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?

[v-yueyun-msft]

Hi , @jwhk 

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

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

[jwhk]

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 "[ ]"?

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 "[ ]".

[v-yueyun-msft]

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

[jwhk]

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?

[v-yueyun-msft]

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:

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

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

Best Regards,

Yueyun Zhang