Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MR84
Frequent Visitor

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:

Screenshot 2021-06-11 142042.png

 

 

4 REPLIES 4
edhans
Super User
Super User

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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. 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
MR84
Frequent Visitor

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

Hi @MR84 - if you are using a matrix, you are using measures. Measures are the values that are in the visual. If you just dragged a field in to the Values section, the Power BI created an Implicit measure, generally not a good idea. Best practice is to create an Explict Measure.

For example, if the table and field you want to analyze is TableName[Sales], if you just drag that field into the visual you will get the SUM of the sales by default. Instead, create a measure that is:

Total Sales = SUM(TableName[Sales])

Same result. But now you can change it, and you may need to here.

Total Sales = COALESCE( SUM(TableName[Sales]), 0)

You cannot do that with an implicit measure. 

Now if the values returns blank, COALESCE will return a 0 instead.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors