Forum Discussion
Custom Power Query Code for refresh not to fail when there is no source data files
Hi Guys,
Is the following possible to implement in Power Query?
I am sourcing set of CSV files from a folder in Azure Blob Storage. Then I am combining these files in Power query before loading the results to the model.
These two scenarios can occur:
1)This source folder may not have any CSV data files sometimes. In this case I dont want refresh to fail.
2)It is also possible that source folder is not present sometimes. In this case also I dont want refresh to fail.
Note: Structure of the file is fixed though.
How can I run the refresh of report sucessufully. Can somebody help me with the M query code for this.
Thanks
Hi Mann
Then you add a last step to your queries that checks for existing errors and in case of error, produces an empty table. Usually it is necessary to have the column names still in place. You can hardcode such a table like so:
#table( {"ColName1, ColName2..."}, { { } } )
The step would look like so:
try <PreviousStepName> otherwise #table( {"ColName1, ColName2..."}, { { } } )
10 Replies
- Hi Mann
This may depend on the environment you are running your code in. This approach 100% works on Dataflow (I use it to sort a similar problem), i am confident it will work fine in Excel, and, I think, it may work on Power BI Desktop/Online.
The general idea is to reference the output table, but if you would reference an external source. I.e. going a full length starting from New Data source button. The trick is that the actual data do not refresh until PBI is satisfied that all queries have resolved successfully and therefore does not overwrite old data before you query finishes.
All you need to do in you code is try loading you CSV data, either check for failure - e.g. RowCount files - or try...otherwise. If the check fails you load the existing data if passes - new data from CSV.
Kind regards,
JBAnonymous
Thanks for your thoughts on this.
Is it possible to do this technically in M query as I can't use Dataflows in current architecture.
If it is possible in M query can somebody give me some sample code to start with?
Mann.
<Looping some of Power Query experts who helped me learn through their impressive solutions in this community>
