Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

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

(0) ShareShare
ReportReport
Posted on by 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.

  • PBILover Profile Picture
    PBILover 14 on at
    Re: Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

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

  • PBILover Profile Picture
    PBILover 14 on at
    Re: Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

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

  • jwhk Profile Picture
    jwhk 115 on at
    Re: Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

    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.

    jwhk_0-1709514655814.png

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

     

    jwhk_1-1709514801611.png

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

  • jwhk Profile Picture
    jwhk 115 on at
    Re: Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

    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.

     

    jwhk_0-1709258291599.png

    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?

     

    jwhk_1-1709258715436.png

    jwhk_2-1709258733716.png

     

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

    jwhk_0-1709259714604.png

     

     

     

     

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

    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 Profile Picture
    jwhk 115 on at
    Re: Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

    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

    jwhk_0-1709176972500.png

    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?

    jwhk_1-1709177070527.png

     

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

    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 Profile Picture
    jwhk 115 on at
    Re: Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

    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?

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

    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 Profile Picture
    jwhk 115 on at
    Re: Combine Two DAX Queries into One CSV Table, and Later Multiple CSV Tables into Different Worksheets in One Excel File

    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,434

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,722

Leaderboard