How can I perform COALESCE in power query?

dev_huesca picture dev_huesca · Jul 14, 2015 · Viewed 12.1k times · Source

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

Alejandro Lopez-Lago - MSFT picture Alejandro Lopez-Lago - MSFT · Jul 14, 2015

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