Become a Data Whisperer

Ready to leave data drama in the dust? In last week’s edition, we learned how to confidently tackle errors with the ‘try’ and ‘catch’ expression. This week, we’re taking the guess work out of errors and transforming you into a data whisperer, someone who can not only handle the unexpected but also navigate and side step data mishaps with ease.

🔮 Becoming the Data Whisperer: Channel Errors into Insights

So how do you become this go-to data whisperer? Well when errors occur, you want to manage them effectively, and one of the best ways to do that is by channelling them into an error table.

What exactly is an error table? It’s a specialized table in Power Query where you can collect and organize information about any errors that happen while transforming your data. Instead of letting errors derail your workflow, you catch them, study them, and understand them—all within this nifty table.

💻 M Code Magic: Crafting Your Error Table

Here’s the M code to create an error table, (click here if you would like to download the code):

Breaking Down the Code:

1. “Source”: Attempts a transformation by using Number.FromText to convert text “abc” to a number, which in this case, will lead to an error as “abc” is not a number.

2. “errorTable”: Next, the script checks whether an error was indeed caught using “source[HasError]”:

• If “HasError” is true, an error occurred during the conversion, and an error table is created. This error table is a single-column table with the header "Error" and contains the error information.

• If “HasError” is false (meaning no error occurred), errorTable is set to null, indicating that there's no error information to store.

3. “finalResult”: Here, a check is performed to determine the content of “finalResult”:

• If an error was detected, “finalResult” is assigned the error table that was just created.

• If no error was detected, it takes the successful conversion result from “source[Value]”.

  However, in this script, “finalResult” will always be the error table because “abc” cannot be successfully converted to a number.

4. #”Expanded Error”:

• If “finalResult” contains an error table, this part of the code takes each error record and expands it into multiple columns to make the details of the error more accessible.

• It creates columns for "Reason", "Message", "Detail", "Message.Format", and "Message.Parameters".

Returning the Result: 

The final result is this error table below that can be used to analyse the error and to understand what went wrong during data transformation.

📥Your Turn: Share Your Thoughts & Topic Wishes

In essence, an error table is designed to safely attempt a data transformation, capture any errors in a structured way, and then provide detailed information about those errors. You can use them to spot patterns, diagnose issues, and even figure out proactive solutions to stop these errors from repeating.

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.

Is this newsletter helping you tame the wild data beasts with Power Query? If you're nodding yes and feeling generous,😊 I would be grateful if you treated me to a coffee. Your support is more than just caffeine; it's the energy that powers our shared quest for query mastery. So, if you're keen to keep the adventure alive, every sip counts! ☕

🏆 Comment of the Day

Please click here if you would like to check out the video that kebincui is referring to, it dives into identifying compromised data and avoiding costly errors.

👀Sneak Peek for Next Week:

Next week we’re discovering Power Query's unwritten rules that'll sharpen your skills and speed up your workflow. Get ready for pure, actionable knowledge – it’s a must-read for anyone serious about data!

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