Coalesce Operator (??) in Power Query
The ?? (coalesce operator) can help us replace missing data (‘null’ values) with something else. When performing calculations, a common requirement is to provide a default value instead of null. Null is a special case that represents the absence of a value and is different from zero.
Syntax
If the return value in Value is null then it should return FallbackValue, else Value is returned.
Example: Let’s first handle the null value with the if and else function in Power Query.
Power Query M
let
MyTable = Table.FromRecords(
{
[CustomerID = 1, Name = "Ashish", Salary = 4567],
[CustomerID = 2, Name = "Katrina", Salary = 9875],
[CustomerID = 3, Name = "Alia", Salary = null],
[CustomerID = 4, Name = "Vicky", Salary = 8479],
[CustomerID = 5, Name = "Mohini", Salary = 0],
[CustomerID = 6, Name = "Meenakshi", Salary = 5746],
[CustomerID = 7, Name = "Esha", Salary = null],
[CustomerID = 8, Name = "Anjali", Salary = 5864]
}
),
Return = Table.AddColumn(
MyTable,
"Salary Status",
each if _[Salary] = null then "Salary Not Found" else _[Salary]
)
in
Return The output of the above code is shown in the image below:
The above power query by using the coalesce-operator can be rewritten as follows:
Power Query M
let
MyTable = Table.FromRecords(
{
[CustomerID = 1, Name = "Ashish", Salary = 4567],
[CustomerID = 2, Name = "Katrina", Salary = 9875],
[CustomerID = 3, Name = "Alia", Salary = null],
[CustomerID = 4, Name = "Vicky", Salary = 8479],
[CustomerID = 5, Name = "Mohini", Salary = 0],
[CustomerID = 6, Name = "Meenakshi", Salary = 5746],
[CustomerID = 7, Name = "Esha", Salary = null],
[CustomerID = 8, Name = "Anjali", Salary = 5864]
}
),
Return = Table.AddColumn(MyTable, "Salary Status", each _[Salary] ?? "Salary Not Found")
in
Return The output of the above code is shown in the image below:
We can see using the coalesce operator makes the code shorter and more readable.