Power Platform Community Forum Thread Details

Run a Query Against a Dataset - How to Replace Null/Blank Values

Hello everyone,

Newbie here in Power Automate. And I've spent the last few days trying to find a material that'd be helpful for my case as I think this might be quite a common issue. But somehow, I can't find one that addresses my problem. Or at least I don't know how to apply it in my case I guess.

So, I am creating a flow to export data from a DirectQuery Power BI dataset into csv file in Sharepoint.

There are around 60 to 70 columns and over 50k rows.

I have followed Curbal's solution for this (Export MORE THAN 100k rows of Power BI data to CSV using Power Automate | No ROW limits! (youtube.com)) and I was able to successfully export all 50k rows in one csv output.

The catch is, when I reviewed the output, some columns had mismatching values. And when I inspected further, I realized that if the first row in the dataset has a blank (or null?) value, those columns with blank or null values are skipped. Then, if the succeeding rows already had values for those columns, they are added back but they get added as the last column in the output. Hence, this messes the arrangement of the columns.. so when you do a run with multiple loops, any loop where the first row had blank values will mess up the final combined output file.

So my thinking is that there has to be a way to replace all null values to maybe a zero or an 'n/a' so that the flow does not skip the column whenever the first row in the loop happens to have a blank value in one or more of its columns.

I have read about potentially adding a COALESCE function, or a "IF(EMPTY(" conditional function to thew flow but all the examples I saw had input data as either manually coded/initialized or from a Sharepoint list. I wonder how to apply the same fix for my case where the data is from a DirectQuery PowerBI dataset.

To visualize,

imagine this is the data from the PBI dataset: