This tutorial will demonstrate how to use the COALESCE operator in Power Query / M language by examining two practical problems. You can watch the full video of this tutorial at the bottom of this blog.
The COALESCE operator is represented by a double question mark (??). This operator works similarly to the or operator in Python or the default function in the Power Query M Language. It returns the first operand if it exists and is not null, and otherwise returns the second operand.
The first problem deals with the conditional replacement of values. For example, if column two contains a null value, the result should return the value in column one. Otherwise, the column will retain the value from column 2.
The second scenario is a challenge provided by Excel BI in LinkedIn.
Excel BI provides daily challenges for Excel and Power Query. But in most cases, Excel challenges can also be solved with Power Query. The goal of the second example is to filter out all the strings that don’t consist of two words.
Example #1: Replace Values Using The COALESCE Operator
Open the Query Editor. Make sure that your Formula Bar and Query Setting panes are visible. If not, go to the View tab and toggle the Layout settings.
In the first example, a table containing two columns is used.
Select Column 2 and go to the Transform tab. Then, click the Replace Values option.
In the Replace Values wizard, don’t make any changes and press OK.
This will generate the syntax you need. All that’s left to do is update the code in the formula bar to show the changes you want.
Using The IF THEN ELSE Construct
In this case, the code needs to look for a null value. If Column 2 contains a null value, then it will be replaced by the value in Column 1.
Once you’re done updating the code, click the check mark beside the formula bar.
This is one of the ways to conditionally replace null values in your table. However, there’s a simpler method you can use, and that’s by using the COALESCE operator in the M language.
Using The COALESCE Operator
The COALESCE operator is represented by a double question mark (??).
You can use COALESCE when conditionally testing for null values.
To use the COALESCE operator, you first need to copy the syntax in the previous example and then click the fx tab found on the left side of the formula bar. This creates a new step in the query.
Paste the syntax and then update it using the COALESCE operator. So instead of using the If Then Else statement, place the double question mark between Column 2 and Column 1.
When Column 2 doesn’t return a null, you’ll get the value from the same column. But when it does return a null, then you’ll get the value from Column 1.
Example #2: Apply Conditional Filters
In the second example, the goal is to filter out data that doesn’t consist two words.
Click the filter option in your table and select a random filter. This will make Power Query generate an M code syntax.
Next, update the syntax to filter out a string that doesn’t consist of two words. Since two words are usually separated by spaces, you need to use the Text.Split command. Then, you need to place it inside a List.Count function so that the result will return a list of values.
Once done, click the check mark beside the formula bar. In the resulting table, you can see that it contains strings with two words. However, there’s an Error in the last row.
An error appears because the code encountered a null value within the dataset. To prevent errors like these from happening, you need to use the COALESCE operator.
So within the Text.Split argument, place the ?? after the Name value and then specify what appears in the table if a null value is found.
***** Related Links *****
Power Query Best Practices For Your Data Model
Comparing Tables In Power Query | Power BI Tutorial
DAX And Power Query | Creating Self-Sorting Columns
Conclusion
In this tutorial, you’ve seen two practical examples where the COALESCE operator can be implemented instead of an If Then Else statement or a Try Otherwise construct.
The COALESCE operator is a useful tool for providing a default value for a variable that may be null or have another default value. It’s often used to avoid null reference exceptions or to provide a default value when working with data that may be missing or incomplete.
All the best,
Melissa de Korte
