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

score 26 · Accepted Answer · answered Jul 14 '15 at 18:31

26

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))

answered Jul 14 '15 at 18:31

Alejandro Lopez-Lago - MSFT

4,014
20
25

What does it fail on? What does your query look like? Could you copy the code shown in the Advanced Editor for the query? – Alejandro Lopez-Lago - MSFT Jul 20 '15 at 18:49
ended up using the nested if solution... not practical, but it works, thanks! – dev_huesca Sep 24 '15 at 14:58
21

I would use `List.Last(List.RemoveNulls({[Q1], [Q2], [Q3], [Q4]}))` – Mike Honey Dec 23 '15 at 05:43
If I could give +10, I would. Thank you! – Jason Jul 25 '18 at 15:41
@MikeHoney why `List.Last` ? Why last, not first. Shouldn't it be [Q1] if its not empty? Or does not Last sellect Q4 if its not null? – Przemyslaw Remin Dec 15 '20 at 20:40
@PrzemyslawRemin you are probably right - I was building on Alejandro's example. – Mike Honey Dec 16 '20 at 07:05

score 18 · Answer 2 · answered Sep 25 '20 at 17:38

18

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.

answered Sep 25 '20 at 17:38

MarioVW

2,225
3
22
28

Wow this is a far more elegant solution for most situations! +1 – QA Collective May 18 '22 at 08:02
This is really good! – Martin Tassara Sep 24 '22 at 20:20

score 2 · Answer 3 · answered Jul 30 '22 at 21:29

It's great how the Power Query M Language has developed over time. And luckily the null coalescing operator has been introduced. It's represented by ??

The easiest way to do a COALESCE is by using the last example. But you can use any of the following methods:

= 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}

More explanation on this you can find here: https://gorilla.bi/power-query/coalesce/

Cheers, Rick

score 0 · Answer 4 · answered Jun 02 '20 at 23:16

0

I think is a little bit late, but I'm also looking for a better solution than: List.First( List.RemoveNulls( { list } ) )

or for a default value:

List.First( List.RemoveNulls( List.Combine { { list }, { defaultValue } } ) ) )

answered Jun 02 '20 at 23:16

user682701

1,441
1
9
4

score 0 · Answer 5 · answered Feb 25 '21 at 07:23

Using List.RemoveNulls bothered me because it seems to do more work than necessary.

Here is a function that appears to avoid processing the whole list, beyond the first non-null item:

coalesce = each List.First(List.RemoveFirstN(_, each _ = null), null)

How can I perform COALESCE in power query?

5 Answers5

Linked