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.

[jwhk]

Hi @v-yueyun-msft,

Thanks for your reply again.

Could you guide me how to put two csv left and right?

The number of rows for these two csv 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?

Right now, I have the following DAX query for the table that I would like to change the 'PVXJD PITTRA'[Date] and 'PVXJD PITTRA'[Delivery No] columns to [Date] and [Delivery No]. Do you know where shall I put the following two statements in the DAX query?

 "Date", 'PVXJD PITTRA'[Date],

 "Delivery No.", 'PVXJD PITTRA'[Delivery No],

[v-yueyun-msft]

HI , @jwhk 

Thanks for your response! I create a test flow in my side and this is the result :

This is my test flow:

(1)The condition is used to judge which query return more rows data:

And you need to see my action name in this screenshot! Below the "Run a query against a dataset" action is "Create csv table 2"! You need to replace the action name in the below expression!

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

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

(2)In "If yes":

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

concat(split(body('Create_CSV_table_2'), decodeUriComponent('%0D%0A'))?[item()], ',,,', split(body('Create_CSV_table'), decodeUriComponent('%0D%0A'))?[item()])

decodeUriComponent('%0D%0A')

(3)In "If no" is similar:

range( 0 , length(outputs('Run_a_query_against_a_dataset_2')?['body/firstTableRows']) )

concat( split(body('Create_CSV_table'),decodeUriComponent('%0D%0A'))?[item()] , ',,,' , split(body('Create_CSV_table_2'),decodeUriComponent('%0D%0A'))?[item()] )

decodeUriComponent('%0D%0A')

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,

For "Sorry, something went wrong." error, I prefer to continue creating the csv file if the array is blank because eventually, I want to create a single Excel file that will put all the csv files (included blank csv files) in different Worksheets. 

If you refer to my previous message, I could open some blank csv files, but not the one created from "Create file 10" action. I also showed you the comparison of "Create file 10" action and "Create file 4" action, where "Create file 10" action has the Size value of 0 and "Create file 4" action has Size value of 4. The result is I could open the blank csv file created by "Create file 4" action, but not the blank csv file from "Create file 10" action.

Do you think this Size value of 0 is the root cause of "Sorry, something went wrong." error? Is it possible to allow the blank array to create a blank csv file that can be opened or added into the Worksheet of Excel file?

[jwhk]

Hi @v-yueyun-msft,

For two csv left and right, I tried to follow your steps, but encountered the following error. Is it because the missing parameters in Condition and Select actions as highlighted in yellow?

The input parameter(s) of operation "xyz" contains invalid expression(s). Fix invalid expression(s) for the input parameter(s) of operation "xyz".

Note: I have removed "Initialize variable" and "Append to string variable" actions from this flow this time.

[v-yueyun-msft]

Hi , @jwhk 

You need to click here and then put the expression :

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

Thanks for your reply.

I still didn't get it.

Just now I checked the Youtube video, and saw they put the expression at "Enter Value" box and a text in "Enter Key" box (refer to the screenshot below).

The icon you highlighted in your screenshot is "Switch to key value mode" which will show the "Enter key" and "Enter value" box. If I click the icon of "Switch to text mode", it will show the text box like the screenshot below.

Could you guide me what is the proper way to configure this "Select" action?

[v-yueyun-msft]

Hi, @jwhk 

Sure , you can just delete this in this mode.

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

Oh I see. I managed to put the following expression in "Map" box now.

concat(split(body('Create_CSV_table_2b')), decodeUriComponent('%0D%0A'))?[item()], ',,,', split(body('Create_CSV_table_2a'), decodeUriComponent('%0D%0A'))?[item()])

The expression I put in "From" box is as below.

range(0,length(outputs('Run_a_query_against_a_dataset_2a')?['body/firstTableRows']))

And this time I'm trying to troubleshoot the expression error by putting one action and save it immediately. So after I put "Select 1a" action, and saved it, I still having the expression error. So I believe it must be something wrong with the expressions I mentioned above. Do you know what's wrong with these 2 expressions?

[v-yueyun-msft]

Hi , @jwhk 

What is your concat(xxxxxx) function?

The range(xxx) seems has no error. And what the error code in your side?

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

Sorry I copied and pasted the wrong expression for "Map" box and I have corrected it (in my previous message).

Here's the concat( ) function I used:

concat(split(body('Create_CSV_table_2b')), decodeUriComponent('%0D%0A'))?[item()], ',,,', split(body('Create_CSV_table_2a'), decodeUriComponent('%0D%0A'))?[item()])

[v-yueyun-msft]

Hi , @jwhk 

What's the error code in your side ? And can you show the expression used in the condition?

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

I found the root cause of the error. I put extra ")" in the expression. 😛

concat(split(body('Create_CSV_table_2b')), decodeUriComponent('%0D%0A'))?[item()], ',,,', split(body('Create_CSV_table_2a'), decodeUriComponent('%0D%0A'))?[item()])

[jwhk]

Hi @v-yueyun-msft,

I encountered the following Action 'Join_2b" failed error while trying to run the test flow.

InvalidTemplate. Unable to process template language expressions in action 'Join_2b' inputs at line '0' and column '0': 'Deserialized JSON type 'Newtonsoft.Json.Linq.JObject' is not compatible with expected type 'Newtonsoft.Json.Linq.JArray'. Path 'from'.'.

Do you know what caused the InvalidTemplate error?

If you look at the latest content of csv file, the length for "Run_a_query_against_a_dataset_2b" is greater than length of "Run_a_query_against_a_dataset_2a". Hence, the condition result is "False" and the flow went to "Select 2b" and stopped at "Join 2b" action with InvalidTemplate error.

[v-yueyun-msft]

Hi , @jwhk 

Can you show me the output of the "Join 2b" action?

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

The only output I could see is the InvalidTemplate error (see the screenshot below).

[v-yueyun-msft]

Hi , @jwhk 

Sorry , i want to see this output , i have put the wrong word..

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

Here's the Outputs of "Select 2b".

I saw it contained the outputs of first table rows of  "Run_a_query_against_a_dataset_2a" and first table rows of "Run_a_query_against_a_dataset_2b".

[v-yueyun-msft]

Hi , @jwhk 

This error code is strange i have not ever encountered.

Now the dataset 2b is larger than the dataset 2a, so you need to use this code in the "Select" action.

concat( split(body('Create_CSV_table_2b'),decodeUriComponent('%0D%0A'))?[item()] , ',,,' , split(body('Create_CSV_table_2a'),decodeUriComponent('%0D%0A'))?[item()] )

And what is the code in your Join action?

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

You meant use the following similar concat() expression for "Select 2b" action?

concat( split(body('Create_CSV_table_2b'),decodeUriComponent('%0D%0A'))?[item()] , ',,,' , split(body('Create_CSV_table_2a'),decodeUriComponent('%0D%0A'))?[item()] )

I tried to change "Select 2b" action with the similar concat() expression as mentioned above, but still encountered the InvalidTemplate error.

The length of "Run_a_query_against_a_dataset_2b" can be greater than the length of "Run_a_query_against_a_dataset_2a" in the beginning of the month, but eventually "Run_a_query_against_a_dataset_2a" will be greater than "Run_a_query_against_a_dataset_2b" towards the end of the month or vise versa.

The code in "Join 2b" action is:

From is outputs('Select_2b')

Join with is decodeUriComponent('%0D%0A')

[v-yueyun-msft]

Hi , @jwhk 

Please try to use this in the Join From:

body('Your Select action name')

body('Select_2b')

Best Regards,

Yueyun Zhang