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(

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:


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]}.


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.

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

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.

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

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!

Table of Content

Table of Content


Improve your PowerQuery skills with
Exceed Academy


Notify of
Oldest Most Voted
Inline Feedbacks
View all comments


Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts


Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more


Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
Would love your thoughts, please comment.x
Scroll to Top