Forum Discussion

MR84's avatar
MR84
Frequent Visitor
5 years ago

Power Automate export to Excel

I have a Power BI Matrix visual setup to present a data table. This will show all the information available however might not show any information in a given cell if there is nothing to show.

 

When I setup Power Automate to export this to excel it will only export the rows of data that have data in each of the columns. For example:

 

Name    Qty

A            1

B            2

C

D            4

 

What i'm finding is the output is:

 

Name    Qty

A            1

B            2

D            4

 

Notice that Row C is missing as it didnt have any data in the Qty coloumn.

 

Below is an example of the flow i'm using:

 

 

Need Help

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    You should probably ask this in the Power Automate forum as someone might be able to help you with the advanced settings to include rows with blanks.

    In Power BI though, if you wrapped your measure with COALESCE() it would return a zero, and then export. So whatever your measure is, use this:

    COALESCE(
   <your entire measure>,
   0
)

    If your measure returns any value, this will keep that value, but if it returns blank, this will convert it to zero. That will make Power Automate work, but may not be visually appealing on the visual in Power BI itself.

    You could have two visuals. One that looks like you want with the blank, then a hidden visual that has the COALESCE in it, and that could be the one Power Automate exports when the button is clicked. 

    • edhans's avatar
      edhans
      Community Champion
      5 years ago

      Please let me know MR84 if this solved the Power BI side of things. If it did, please mark it as the solution so the thread can be shown to be solved - or at least handed off to the Power Automate community. 😁

      • MR84's avatar
        MR84
        Frequent Visitor
        5 years ago

        edhans i must admit your solutions was over my head being a newbie. 

        I'd say one thing however and this might be my basic understanding of PBi at present. The Matrix visual i am using does not have any measures in it - its all based on relationships between other tables.

         

        So as an example, in my data the customer name might come from one table and if it exists in another table will provide a particular peice of information, however if it doesnt appear in 3rd table nothing will be returned which is where the blank information is appearing from. 

         

        I think from my playing around its potentially a flaw in the Power Automate feature within Power Bi as i have other Power Automates working with other data that dont behave in this way.

         

        Thanks you for your pointer however.