Recently I was asked an interesting question by Ondra Plánička in the comments of a blog post: how can you handle errors caused by unavailable or missing data sources in Power Query?
Let’s imagine you are loading a csv file like this one into Excel using Power Query:
The M query generated by Power Query will be as follows:
If you load into the Excel Data Model you’ll see the following in the Power Pivot window:
So far so good. But what happens if you try to refresh the query and the csv file is not there any more? The query refreshes but you will see the following in the Power Pivot window:
The structure of the table that has been loaded has changed: instead of three columns you get just one, containing the error message. This wipes any selections in Excel PivotTables that are based on this table; they will need to be recreated when the source file is available once again. Similarly, any relationships between this table and other tables in the Excel Data Model get deleted and have to be added again manually when the source file is there again. Not good.
Here’s how to alter the query so that it handles the error more gracefully:
While the code from the original query remains intact, the following extra steps have been added:
- The AlternativeOutput step returns a table (defined using #table) that has exactly the same columns as the csv file. This table has one row containing the text “Error” in the two text columns and 0 in the Sales column.
- The TestForError step uses a try to see whether the Source step returns an error (for example because the file is missing)
- The Output step checks to see whether TestForError found an error – if it does, it returns the table defined in the AlternativeOutput step, otherwise it returns the contents of the csv file as returned by the #”Changed Type” step.
Now when you run the query and the csv file is missing, then you see the following in the Power Pivot window:
Because this table has the same structure as the one the query returns when the csv file is present, any PivotTables connected to this table will still retain their selections and the relationships in the Excel Data Model are left intact. This means that when the csv file is back in its proper place everything works again with no extra work required.
You can download the example workbook and csv file here.