Forum Discussion
Power Automate export to Excel
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.
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. 😁
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.
