Error Handling (like Try-Catch in SQL)

[Anonymous]

Hey guys,

in my project i have a huge sales dashboard with many data sources and querries (SQL-Database, Web-API, OData-Feed, Excel...). One data source is a excel file. The dashboard is live, important for the sales department in their daily work and is refreshed 3 times per day. I fear huge problems (the whole dashboard could crash?), if anyone changes things in excel (e.g. changing columns or changing the sheets).

I know from SQL or Python the error handling method "Try-Catch". My idea was, to implement something like that in the power querry for the excel querry. 

Is this possible or do you have any other ideas for this problem?

Thanks for your help;)

[Anonymous]

Thanks to all for your help. This was a great start/input to solve m problems 😉

[BA_Pete · Accepted solution]

Hi @Anonymous ,

To do this at the code level in M, you use the try...otherwise structure e.g.

try [Value] / 10 otherwise null

To do this at the whole query level you can try these methods:

Ensure columns always present:

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

Handle source qury errors:

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

Make sure to read the comments after each of these blogs carefully, as other users add crucial amendments to the original code.

Pete

[AlexisOlson]

Ben Gribaudo also has a valuable article on error handling.

https://bengribaudo.com/blog/2020/01/15/4883/power-query-m-primer-part-15-error-handling