Dynamic Coalesce in PowerQuery

We can define coalesce in PowerQuery as the last value existing in a single row of multiple columns positioned from left to right. In our example that would be the last value from the month columns. In this article, we will show you how to create both static and dynamic formulas for coalescing. If you wish to follow along, you can download the Excel file with the code here.

Coalesce Static Solution

If we were to create a static version of the code (which works only for columns Jan-Jun) then the PowerQuery code would look like this:

= Table.AddColumn(Source, "Coalesce", 
                     each List.Last(
                                    List.RemoveNulls({[Jan],[Feb],[Mar],[Apr],[May],[Jun]})
                                    )
                  )

We can read the code above as follows:

1. Table.AddColumn() function adds “Coalesce” column to an existing PQ Source table variable. Table.AddColumn() is an iterating function meaning it creates a row context
   1. For each row of the table, the function pushes current row value from columns [Jan] – [Jun] into a list of values (the curly brackets are needed to form a list of those values)
   2. List.RemoveNulls() function removes all null values from the list
   3. List.Last() function returns the last non-null value from the remaining list values
2. The result of List.Last() function is returned to newly added “Coalesce” column for each row.

How to Make it Dynamic

The problem with the code above is that it is static, meaning if a new month is added to the source, the code will not pick it up and will continue to create coalesce only on columns [Jan]-[Jun]. We need to make the code dynamic by changing the following part of the formula:

{[Jan],[Feb],[Mar],[Apr],[May],[Jun]}

We need to make this part somehow dynamic. The problem is that column names are not text values, but record field values, meaning we cannot push a list of text to create a dynamic list of column names. Instead, we need to use a special function called Record.SelectFields().

Record does not have a column name, but field name. since in our example, the record is actually a single row of iteration over the Source table, then the field names of the record equal the name of the table columns upon which we are iterating.

Record.SelectFields() allow us to add a list of column names as text, and the function will translate that text to a field name. Now we can use the following code to return the same syntax as with {[Jan]..[Jun]}.

Record.SelectFields(_,{"Jan","Feb","Mar","Apr","May","Jun"}))

Underscore is an unnamed variable we use in conjuction with each keyword, meaning for each row the underscore variable is holding the record values of that row.

We now have a list of values as a function argument. Instead of hardcoded values, we can also provide a dynamic list of names we could have created and stored in a separate variable.

let
    Source = Excel.CurrentWorkbook(){[Name="Coalesce"]}[Content],
    ListOfCoalesceColumns = List.Select(Table.ColumnNames(Source),each _ <>"Name" and _ <>"Type"),
    AddRecordForEachRowOfSourceTable = Table.AddColumn(Source, "Coalesce", each Record.SelectFields(_,ListOfCoalesceColumns))
in
    AddRecordForEachRowOfSourceTable

This code would provide us with the table as in the picture above. For each row of the Source table, we would receive a record object holding a dynamic number of field values as defined in a ListOfCoalesceColumns variable.

Finishing the Dynamic Solution

The hard part is over. We can now transform records into lists and find the last non-null value for each list.

let
    Source = Excel.CurrentWorkbook(){[Name="Coalesce"]}[Content],
    ListOfCoalesceColumns = List.Select(Table.ColumnNames(Source),each _ <>"Name" and _ <>"Type"),
    AddRecordForEachRowOfSourceTable = Table.AddColumn(Source, "Coalesce",each List.Last(List.RemoveNulls(Record.ToList(Record.SelectFields(_,ListOfCoalesceColumns)))))
in
    AddRecordForEachRowOfSourceTable

Let us focus on the last step. We already explained how we used Record.SelectFields() to return record value containing months values for each row of the Source table. We now have to use Record.ToList() function to transform record values to list from which we later remove null values, and finally use List.Last() function to return the last non-null value for each row of the Source table.

The part of the code above you need to adjust based on your column names is the step ListOfCoalesceColumns. For the demo, we used a simple filter over the list of Column names, but you can implement a more robust logic that would fit your needs.

YouTube Video

We also have a YouTube video posted on this topic, so check it out!

Hope you enjoyed our article! If you have any questions, please post them below. Feel free to share the article if you think it could help your peers as well!

You can find the working file on the following link.