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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mann
Resolver III
Resolver III

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

1 ACCEPTED SOLUTION

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..."}, { { } } )

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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,
JB

@Anonymous 

 

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>

@ImkeF 

@MarcelBeug 

Anonymous
Not applicable

Hi @Mann 

 

As I said the sample will vary depens on where you run your code. Is this Excel or Power BI Desktop?

 

Thanks,

JB

Hi @Mann ,

what should actually happen for the 2 occurrances you mentioned: Do you want to keep the old data or do you want to create an empty table?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF 

 

Thanks for the reply.

 

I want to create an empty table for these two occurences.

 

Mann

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..."}, { { } } )

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks @ImkeF

It worked as expected and I didnt know single line of code can do this. Thanks for your help.

Mann
Anonymous
Not applicable

Hi @ImkeF 

 

Do you know a solution to another scenario - return old data (vs. return an empty table) in Power BI? I can solve it in PBI Dataflows and Excel PQ, but can't figure out for PBI Desktop.

 

Thanks,

JB

 

 

 Hi @Anonymous 

easiest would be to let it run into error, so the old data would be kept.

But if you're interested in incremental load, this article leads you to a couple of hacks/workarounds for Power BI: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/ 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@Anonymous 

 

I want this code to run in Power BI Dekstop.

 

Mann.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors