en de en de de en de en id de en nl en en en en en de de en de de de de de en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en de de de de de de de de de de nb de pt en en en de de de en en de de de en en ru en en en en de de de en en en de de de sv fa it en de de de en nl de en en en en en en de en nds de it de en de en de en
I can recall that back then, in the good old Excel VBA days, at some point, I was very busy with error handling to make it easier for the user to understand the logic of my program. One article was particularly illuminating for me: Error handling via an error class, by Dick Kusleika.
The languages M and VBA don’t have much in common, but also in M I want raise custom errors, when if something contradicts the business logic. Providing the user with a meaningful error message can greatly increase the acceptance of the program. This article deals with how you can gracefully handle custom errors in M.
Recommended ressources about standard error handling in M
Of course some M-enthusiasts in the community have already dealt with the topic of error handling and I recommend you to have a look at these as well:
Miguel Escobar highlights in the first of the mentioned posts, that you can raise your own (custom) error messages, if you want to.
What are custom errors and when are they useful?
Standard error message in M
But what if you want an error message to be returned on an expression that could be evaluated by M but doesn’t fit into your business logic?! In the following example, I want an error message to be returned whenever the tax percentage is not 7% or 19%.
Throwing a custom error in M
The purpose of custom errors is to return error messages when the expression is technically evaluable but contradicts your own business logic. In this case, it is good and right to provide the user with the right information so that he can correct the error as quickly as possible. Let’s see how this can be done.
You can throw a custom error by using the keyword ‚error‘ followed by the function Error.Record(). Within the function you can define up to 3 arguments, which are:
optional message as nullable text and
optional detail as any
The following screenshot shows how to manually generate this error message in a calculated column:
Throwing a custom error by writing the Error.Record manually
But what if I want to check for this (logical) error in many places in my queries? Do I then have to write this Error.Record manually each time, or copy it from one of my previous solutions? To avoid this manual process, I have written my own function that fixes this problem!
Using a custom error function for convenience
Whenever it makes sense, I outsource tasks to custom functions that I can then reuse. This reduces the risk of errors, makes my code clearer and easier to maintain. My goal in calling my custom error therefore looks like this:
Throwing a custom error by calling a custom function and the passed custom error ID
So instead of manually rewriting (or copying) the Error.Record at any necessary point in my code, I want to define it centrally at one point and then call it (based on its ID) via a function from any of my queries. I’ll show you how to do this now.
I have commented on the functionality of my function directly in the source code and hope that this is sufficient to understand it.
I hope this was interesting for one or the other. I am sure that this method can be further developed. If you succeed, please let me know.
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
Kategorie: Technical Topics (english) Stichworte: error handling, M, Power Query
Lars Schreiber bei So passt sich Deine Kalendertabelle automatisch an neue Jahre an
Nico Thormann bei So passt sich Deine Kalendertabelle automatisch an neue Jahre an
RaiSta bei Get your own Power Query Editor using Notepad++
Lars Schreiber bei Tabellenüberschriften massenhaft und dynamisch umbenennen mit Power Query
Daniel bei Tabellenüberschriften massenhaft und dynamisch umbenennen mit Power Query
30. September 2020 um 8:16 am
Yes, I see the point.
Than maybe create a table for error thought Enter Data form in PQ with four columns (reason, message, detail, InternalDescription) convert it to records, and filter the record Id?
It would be easier to maintain.
30. September 2020 um 8:22 am
I am aware of those possibilities. This solution is more a thought-provoking impulse than a mature, ready-made solution. 🙂
30. September 2020 um 7:55 am
Maybe we do not have to convert error record to table.
Just filter the record by its id.
ErrorRecord = try ListCustomErrors{IDCustErr-1} otherwise error Error.Record( „Error ID not existing!“, „The error ID doesn’t exist! Use an existing one!“),
Result = error Error.Record( ErrorRecord[reason], ErrorRecord[message], ErrorRecord[detail] )
30. September 2020 um 8:10 am
yeah, for sure. You’re right. In the development process, it was convenient to see the error messages in a table, but for the performance of the function your way is shorter, even though I don’t expect a big performance impact, because this error table won’t get thousands of rows long (I hope ;-P ). Thanks
Schreibe einen Kommentar Antworten abbrechen
Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert
Lists, Records and Tables in M - how, when and why
Writing Documentation for custom M functions
The Environment concept in M for Power Query in Excel and Power BI Desktop
Creating asymmetric Pivots and control them by Slicer
Über THE SELF-SERVICE-BI BLOG
Ich bin ein freiberuflicher Power BI-Berater, -Entwickler und -Trainer und wurde von Microsoft mehrfach mit dem MVP Award ausgezeichnet. Ich arbeite mit einem kompetenten Netzwerk aus freiberuflichen und ambitionierten Kollegen zusammen. Erfahre hier mehr.