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

(0) Share

Report

Posted on by jwhk

115

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.

Categories:

General topics

Screenshot 2024-0...

I have the same question (0)

All responses (72)

Answers (0)

v-yueyun-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
jwhk 115 on at

Like (0)

Report

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?

DAX Query1.docx

DAX Query2.docx

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
jwhk 115 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (1)

Report

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

Was this reply helpful? Yes No
jwhk 115 on at

Like (1)

Report

Hi @v-yueyun-msft,

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

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (1)

Report

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

Was this reply helpful? Yes No
jwhk 115 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
jwhk 115 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No