How to Use the Coalesce Operator in Power Query

I had a task to replace Null with the first non-null value from another field, and that’s when I employed the Coalesce operator in Power Query. As we all know, we can use Coalesce as a function in DAX, and we also have the Coalesce operator in SQL. However, not everyone is aware that it can also be utilized in Power Query.

So what is Coalesce?

The null Coalesce operator ?? enables the substitution of an alternative value in place of null. This proves beneficial in scenarios such as calculations where a default value is often needed instead of null. Null, delineating the absence of a value, is distinct from zero in its nature.

The M-language features the Coalesce Operator to address handling nulls. It’s represented by ??.

Simple example:

(based on the Adventure Works dataset)

The values in this new column are determined by using the Coalesce operator ?? on each row’s “ProductDescription” and “ModelName” columns. If “ProductDescription” is null, it uses the value from “ModelName” as a fallback.

In conclusion, the COALESCE operator proves to be a valuable tool for assigning a default value to a variable that could be null or have another predefined value. Its common application includes preventing null reference exceptions and offering a default value when dealing with potentially absent or incomplete data.

PowerBI #PowerQuery #Mlanguage #Coalesce