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 

You need to first put the value of the get files properties in the apply to each:

Also you can also use the expression:
items('Apply_to_each')?['{Identifier}']

Best Regards,

Yueyun Zhang

[AlexEncodian]

@jwhk 

I would switch back to the old UI designer. The dynamic property names are different in the UI designer and its very buggy especially with apply to eachs. Then you should be able to see the same properties.

[jwhk]

Hi @AlexEncodian and @v-yueyun-msft,

Thanks for your replies yesterday.

Now I'm stuck in Step 7 (Merge Excel and CSV Files with Power Automate — Encodian).

I noticed my "Merge Excel Files" action looks different than the one in Encodian website.

1) This screenshot is from Encodian website

2) This is from my Power Automate

Could you advise me what shall I enter in Connection Name, API Key an Region?

[v-yueyun-msft]

Hi , @jwhk 

First of all, we should state that in the forum, we usually solve one problem in one case. If you have other questions, you can create a new case so that you will get a faster and more accurate response!

And for the Encoding connection, you need to  sign up for trial via our website:

One Flow for Multiple SharePoint Sites - Encodian Trigr

Then you can see the API key here:

And for more information, you can refer to :
Create an Encodian Connection in Power Automate for Trigr – Encodian Customer Help

And the Connection Name is just "Encodian".

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

Thanks for your reply.

If you read the title of this case, it covered 2 problems here. First problem is completed/fixed now (Thanks to you again).

Now the second part is multiple CSV tables/files into different worksheets in one Excel file. Shall I create a new case for this?

For the "Merge Excel Files" action, is there any alternate ways doing this without using the premium connector such as Encodian?

[v-yueyun-msft]

Hi , @jwhk 

In my work experience , there is no directly way to merge the multiple CSV tables/files into different Worksheets in One Excel File. In my understand , you need to first convert the csv file to Excels and then merge the excel files using the Encodian's  "Merge Excel Files" action.

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft,

Thanks for your reply.

Currently the DAX queries were converted into .csv files. Do I have to convert them to Excel file first before merging them into 1 single Excel file? Refer to Merge Excel and CSV Files with Power Automate — Encodian, it said it can merge multiple CSV files into 1 single file. 

The Encodian's "Merge Excel Files" action will still running after 30-days trial of Encodian Flowr?

[v-yueyun-msft]

Hi , @jwhk 

As i test before for this action. When you merge multiple csv files , it will not merge into one csv files which do not have  different Worksheets because the worksheet is the unique feature in xlsx files.

It just append the file content in the one csv file in a one sheet.

You can test it in your side also in the  30-days trial of Encodian.

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft & @AlexEncodian,

I have signed up for 30-days trial of Encodian Flowr/Trigr and refreshed the API Key in API Key Management

But after I entered the Connection Name (same as description of API Key) and API Key, and clicked "Create New" button, I encountered "Connection creation/edit of 'shared_encodiandocumentmanager' has been blocked by Data Loss Prevention (DLP) policy 'Global Policy.'" error. Is this blocked by my company's DLP policy?

[v-yueyun-msft]

Hi , @jwhk 

According to your screenshot , it is your company's DLP policy blocked to use this action.

For more information, you can refer to :
Create a data loss prevention (DLP) policy - Power Platform | Microsoft Learn

Best Regards,

Yueyun Zhang

[jwhk]

Hi @v-yueyun-msft & @AlexEncodian,

Thanks for your replies.

Due to DLP policy, I have to give up the "Merge Excel Files" action in my Power Automate flow.

So now after all the 10 "Create file" actions, I proceed to create "Initialize variable" action, follow with "Get files (properties only)", "For each", "Get file content", "Append to array variable" and "Send an email (V2)" actions. These steps are similar to Step 3 to Step 6 in Merge Excel and CSV Files with Power Automate — Encodian.

However, I noticed the email didn't attached the 10 .csv files (from SharePoint folder) into the email.

If you look on the flow result below, you can see "Get file content" and "Append to array variable" actions were skipped with ActionConditionFailed messages (see below).

Can you advise what went wrong on these 2 actions?

These are the settings I set in "Get file content" and "Append to array variable" actions:

[jwhk]

Hi @v-yueyun-msft,

My current flow is back to the early month cycle where some DAX queries will return blank value or create the csv file with 0 size. Subsequently, "Sorry, something went wrong" error will appear when trying to open the csv file.

Previously you advised to create a Condition action if the array is blank then it will not create this file.

Can we have the Condition to insert some data such as ",,," or "0" to the csv file instead of not creating the csv file?

[PBILover]

@jwhk you got the answer on combining different csv tables in a single csv file on a different table

[PBILover]

@v-yueyun-msft : Is there any way 2 combine 2 csv tables in one csv file(both csv tables will be on separate tab of single csv file)?.