Dynamic Coalesce in PowerQuery

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

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!

Table of Content

Table of Content

LEARN POWER QUERY

Improve your PowerQuery skills with
Exceed Academy

COMMENTS

Subscribe
Notify of
guest
2 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments

GET LATEST BI NEWS TO YOUR INBOX

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

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

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

Read more

Dynamic bulk insert to Power Apps collection

In one of our latest blog posts, we described a technique used to copy tables from other programs (e.g. Excel) into PowerApps collections. This solution had a drawback. Within the code, the column order was referenced to determine which column to collect. This means that, for the first column in...

Read more

Bulk insert to Power Apps collection

In this blog, we will show you the technique used to collect tables for your app by using the bulk insert. We will show you how to make the same table structure in Power Apps as Excel tables. We will use Excel to show you how to copy-paste tabular data...

Read more

Why should you start using Power Apps?

In this article, we will take a look at what is Power Apps, why should we use it, and when organizations can benefit from custom apps created in the Power Apps platform.Many organizations are not yet familiarized with Power Apps capabilities, and the ways it can improve their agility, modernize and automate manual processes.   What is Power...

Read more
2
0
Would love your thoughts, please comment.x
()
x
Scroll to Top