Avoid Error Message in Excel, when Power Query Import is not available

[nicolas_her]

Hello All,

I tried to optimize a KPI which is updated automatically under Excel.

The Excel KPI is created with Requests / Links (Power Query Import) from CVS file and the connections are updated automatically every 20 min.

The CSV files are updated every hour on an intranet network. Problem : Sometimes files are missing during 10 minutes.

If the link from Excel / Power Query to CSV is updated during this time, I have an error message : "Source missing".

And I don't find anywhere, in Power Query or Excel, how to avoid this error message or a solution that test the availability of the Source File to update the KPI. (If unavailable, no update and of course no error message).

Many thanks for your feedback.

Regards.

Nicolas

[BA_Pete]

Hi Nicolas,

To do this type of thing in Power Query you generally set up a dummy source that just contains your table schema, then switch to that if there's a source error.

Check out Chris Webb's blog on error handling in Power Query to find the solution that works best for your scenario.

Make sure to read the comments too as there's important improvements logged there:

https://blog.crossjoin.co.uk/2015/07/03/ensuring-columns-are-always-present-in-a-table-returned-by-p... 

https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/ 

Another possible alternative is to run your CSV refreshes in Dataflows.

Pro: Retains previous refresh dataset if current refresh fails.

Con: Adds another step into your refresh process, as you will need to update the dataset after the Dataflow has refreshed (can really easily use Power Automate to push the dataset refresh after DF refresh complete.)

Pete

[nicolas_her]

Ok, thanks, I will check 😉