Gracefully Handle Errors with this Dynamic Duo

So you’ve meticulously crafted your query, and when it’s time to hit refresh an error rears it’s ugly head! You sigh, maybe facepalm a little, but hey this is part of the coding life right? 😅 But what if I told you there’s a dynamic duo ready to come to your rescue, much like those two 👇 jumping into action, hammer and shield in hand.

🦸‍♂️Enter the dynamic duo, try and catch! 

In data transformations, "try" is used to handle potential errors. When you wrap an expression with "try", Power Query executes the expression, and if an error is encountered you can then provide an alternative action using the “otherwise” function. In Power Query “otherwise” is the “catch” of this duo.

🧐Understanding the “Try” Statement

- First let’s understand “try”: it attempts the expression you provide and it then returns a record with two fields:

• Field 1 → “HasError”: a boolean that indicates whether an error occurred

• Field 2 → Will either return “Value” or “Error”

  “Value” is the result of the expression if no error occurred

  “Error” if there was an error it returns a record with the error details

In example 1 below Number.FromText will attempt to convert the text “123” to a number and as it’s a valid number, the value field will be returned.

In example 2 below Number.FromText attempts to convert “abc” to a number but as it’s not a valid number, the Error field will return the record that contains the error details.

If however an error is returned you would want your query to still continue. Enter “catch” or as it’s known in Power Query, “otherwise.”

🚀Handling Errors with ‘Otherwise’

In many programming languages, if “try” returns an error, “catch” will then handle that error instead of the program crashing.

Power Query doesn't have a "catch" keyword, instead it has "otherwise" which serves a similar purpose to “catch.” If an error occurs in the "try" evaluation, "otherwise" specifies what to do next, usually by providing an alternative value or action.

Example 3 illustrates using try and otherwise so that the query can continue (you can click here to download the code):

📥Your Turn: Share Your Thoughts & Topic Wishes

Remember, every error is an opportunity to learn and refine your queries further. Embrace them, understand them, and then conquer them with the prowess you've gained today.

I would love to hear if you enjoyed today’s newsletter, please hit reply and let me know:

• 💌Loved it!

• 😐It’s okay

• 👎Do better

Also if there’s anything you would like me to cover please let me know.

If you missed last week’s newsletter, you can check it out here.

Has "The Query Editor" become your trusty sidekick in the quest to conquer data challenges? If you find yourself smiling in agreement and wish to give back, please consider sharing a coffee with me. It's not just a warm, delightful drink; it represents your invaluable support that energizes our collective journey towards becoming Power Query wizards. If you're on board to fuel more discoveries and insights, remember, each cup makes a mighty difference! Cheers to our continued exploration! ☕

🏆 Comment of the Day

Please click here if you would like to check out the video that jayantchorghe3634 is referring to, this video dives into errors experienced with dates in Power Query and how to effectively fix those errors.

👀Sneak Peek for Next Week:

Next week, we’re taking the guess work out of errors and turning you into a data whisperer - someone skilled not just in managing the unexpected but also in smoothly navigating and sidestepping data mishaps with ease.

Until next time, keep those queries rolling, and may your data always be clean and your transformations swift!

Happy Querying!

Thanks for reading. Until next week!

Miss Microsoft