How To Dynamically Merge Columns In A Power Query Table

by | Power BI

In this tutorial, we’ll examine an interesting question that was raised on the Enterprise DNA Forum about how to dynamically merge a changing number of columns in a Power Query table. You can watch the full video of this tutorial at the bottom of this blog.

This scenario deals with raw data exports that cause the data to spill into an unknown number of adjacent columns. When you look at the example, you can see the description has spilled over into Column 4, and Description 2 has been split over two adjacent columns. But that won’t always be the case. In the next cycle, this number of columns could change.

Power Query Table, power query combine columns screenshot

Before we jump into the solution, let’s examine the M code that Power Query generates when you merge columns.

Power Query Table M Code

There’s a couple of ways to merge columns. One is to select both the description column and pressing down either shift key or control key then select column 4 as well. Then, right-click and choose Merge Columns.

Power Query Table, power bi merge columns screenshot

Or, on the Transform tab, select merge columns. And this dialog box will appear, which allows you to select the Separator.

Power Query Table, power bi combine columns screenshot

In the pop-up box, choose Tab, and you can enter a new column name. In this case, I named it Description.

In the formula bar, we see the M code that this transformation step has created.

Power Query Table

If the formula bar is not visible on your screen, go to the View tab and turn it on.

combine columns in power query screenshot

So the function that Power Query uses to merge columns is Table.CombinedColumns. The first parameter it takes is a table. That table is returned by the previous step in our code, so in the Applied Steps, you’ll see that this is called Source.

Then, it hard-coded the column names in a list. Here you see list initializers, and between in-text values, the column name description, and the column name column 4. Then, it calls another M function to combine the text values in those columns.

And finally, it passes the new column name as a text so we can adjust that.

So if we want this function to dynamically merge a changing number of columns, we’ll have to change that second parameter value that now contains a list of hard-coded column names.

Merging A Changing Number Of Columns In A Power Query Table

I’ll step back to my raw data query, and if we take a closer look at the column names, we see that each of the spill columns is anonymous. They don’t have proper names, but they all start with the text column, followed by a number. Let’s see if we can use that. First, I’ll create a reference by right-clicking here and selecting Reference.

Power Query Table

And to get the column names inside the formula bar, I can add Table.ColumnNames. This function returns a list with all the column names from that table. Let’s turn this back into a table by clicking on To Table.

In this pop-up box, click OK.

Then, on the Add Column tab, I’ll select Format, and then Trim.

Power Query Table

Now, this isn’t a transformation that I want to perform, but it does generate the bulk of the M code for me. All I have to do is replace that trim function with my own logic.

Power Query Table

So inside the formula bar, instead of Text.Trim, we can say if Text.StartsWith, and then point it to our Column 1, and I’ll copy that immediately. It wants that text that we’re looking for, so that’s going to be Column. So if it starts with a text column then we want “null” – for everything else, we want whatever is in Column 1. We can also rename that column, so inside the formula bar, instead of Trim, let’s call it GroupColumn.

Now, all we need to do is fill them at these values. So I’ll right-click my header, select Fill, and Down.

So the next time data comes in and that number of columns has changed, this group will automatically pick it up.

Power Query Table

Let’s rename this query, I’ll call it column groups.

Let’s make sure that its load has been disabled because this is just a supporting query.

I can reference this supporting query and I’ll rename this later. Let’s leave that for now. So if you remember, we started by merging two columns and that created a hard-coded list with the column names, but now we can filter on Description in our GroupColumn.

So if we do that filter on the Description inside Column 1, we get the columns that meet that criteria.

Power Query Table

However, this is a table and not a list. We need to extract whatever remains in that first column because we need to include those columns in our merge operation. So to do that, we can right-click our Column 1 header and select Drill Down.

And now we get a list. So let’s give this query a proper name (ListDescr) and let’s check if it’s disabled from load.

Now we can step back to our Result query and replace that second argument here with our dynamic list. So here, we can reference our ListDescr.

Power Query Table

Let’s repeat this for Description 2 as well. I’ll go to my ColumnGroups query. I’ll create another reference and select the columns that I need. Then I’ll filter Description 2, which will return these three columns. I’ll right-click my header and select Drill Down and rename this query as well (ListDescr2).

I’ll then step back to my Result query, select Description2 column, and press on either shift or control to select the next column as well (column2). I’ll right-click the header and choose Merge Columns.

In the pop-up box, I’ll choose Tab as the Separator and I’ll call this Description2.

I’ll just change the column name here on the formula bar. I’ll change that hard-coded list as well from the list we’ve just created. And now, as you can see, this has also picked up column 8 that we previously excluded.

Power Query Table

***** Related Links *****
How To Merge Queries In Power BI
Power BI Query Parameters: Optimizing Tables
Sorting Date Table Columns In Power BI

Conclusion

In this blog, I’ve showed you how to use M codes in Power Query to dynamically merge columns. It’s not difficult as long as you are familiar with how the Power Query works and what M codes to use.

I hope you’ve enjoyed this one. Please check out the links below for more related content.

All the best!

Melissa

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.