The Query Editor
Posts
Complex M Code Getting You Down? Ease Your Burden with This

Complex M Code Getting You Down? Ease Your Burden with This

A Beacon of Simplicity

Miss Microsoft
December 13, 2023

Hello Data Wizards! 🌟

Are you struggling with complex M code? I wouldn’t blame you, complexity often creeps in uninvited, especially when there’s missing data involved. But fear not, hope is at hand! Today we’re going to cut through the noise with just two keystrokes!

💡Today's Goal: Simplify your M code and boost data integrity using efficient techniques with the “??” operator.

🔍 The "??" Operator: A Beacon of Simplicity

The "??" operator, more commonly known as Coalesce, is your shortcut to simplicity. It provides a straightforward way to ensure that your query can run without errors due to missing data and without the need to write complex code.

💭 How does it work?

It allows you to provide a sequence of alternatives. Power Query evaluates each alternative and returns the first non-null value in that sequence.

In today’s example we will see it in action where we need to sum two columns and one of them has null values. Please download the practice file here.

🚩 The Problem:

Example 1 below shows the results if we add the “Revenue” and “Adjustments” columns. The problem is that nulls are returned due to missing data in one of the columns. This now compromises our data integrity. If you would like to understand why nulls are returned, please read this newsletter: Can You Trust Your Choices with Unreliable Data.

🔧 The Complex Fix:

Example 2 provides the “if” statement that you could write in a Custom Column to ensure that your values correctly sum up when null values are present.

✨ The Simple Fix Using "??"

However example 3 provides a much easier code using the coalesce operator:

Breaking down the code:

“([Adjustments] ?? 0)”: this expression checks if the value in the [Adjustments] column is null

➡️if it is null it returns a 0 for that column and the expression then proceeds to sum the [Revenue] column with 0

➡️if it is not null it returns the value in the [Adjustments] column and the expression then proceeds to sum the [Revenue] column with the value in the [Adjustments] column

🚦 You Have Choices:

Example 4 also uses coalesce, but instead of 0 being used in the place of null, 500 was used in it’s place. So you can replace your null values with whatever value you require:

In essence, the coalesce operator, “??”, is a straightforward yet effective way to perform calculations accounting for the possibility of null values. This method maintains data integrity and prevents errors that might occur due to missing data.

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