How can I perform COALESCE in power query?

Question

I have a table that has values for quarters and I need to add a new column that gives me the last not null value as last quarter. For example

ID | Project  | Q1   | Q2   | Q3   | Q4   | Current Quarter Value  
1  | bal bal  | 23   | 32   | 34   | null | 34  
2  | cuz cuz  | 43   | 56   | null | null | 56  

Answer 1

You can try the null coalescing operator (??). Both of the following lines achieve the same effect of returning ValueA unless it is null, in which case ValueB is returned.

if ValueA <> null then ValueA else ValueB
ValueA ?? ValueB

Applied to your specific case the necessary code would be:

[Q4] ?? [Q3] ?? [Q2] ?? [Q1]

Note that the null coalescing operator is a new addition to Power Query. It has not been officially documented yet and may not be available in all environments.

Information taken from this blog post.

Answer 2

Just for fun, you can use any of the following ways to write it:

<pre><code>

= null ?? null ?? 1 ?? 2

= List.First( List.RemoveNulls( { null, null, 1, 2 } ) )

= List.First( List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ) )

= List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ){0}

= List.Select( { null, null, 1, 2 }, each _ <> null ){0}

 </code></pre>

Hope that helps. You can find more on it right here: The COALESCE Operator in M / Power Query - Gorilla BI

Rick de Groot

Answer 3

There are a couple formulas you can use when adding a custom column to the table (accessible from the Transform ribbon tab). Here's one:

if [Q4] <> null then [Q4] else if [Q3] <> null then [Q3] else if [Q2] <> null then [Q2] else [Q1]

If you don't want to write so many if statements, you can add the columns to a list and filter out the null values:

List.Last(List.Select({[Q1], [Q2], [Q3], [Q4]}, each _ <> null))