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 may watch the full video of this tutorial at the bottom of this blog.
This scenario deals with raw data exports that cause data to spill into an unknown number of adjacent columns. When we look at the example, the description has spilled over into Column 4, and Description 2 has split over into two adjacent columns. But that won’t always be the case. In the next cycle, this number of columns could change.
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.
Or, on the Transform tab, select merge columns. And this dialog box appears, so you can select the Separator.
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.
If the formula bar is not visible on your screen, go to the View tab and toggle it on.
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 that is called Source.
Then, it hard-coded the column names in a list. Here you see this lists 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 actually anonymous. They don’t have proper names, but all start with the text column, followed by a number. Let’s see if we can use that. First, I’ll create a reference. So I’ll right-click here and select Reference.
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.
Now, this isn’t a transformation that I want to perform, of course, 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.
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 a null. 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 this 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 that up.
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.
And 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.
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 do 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.
Let’s repeat this for Description 2 as well. So I’ll go to my ColumnGroups query. I’ll create another reference and select the columns that I need. Then filter on Description 2, which returns these three columns. I’ll right-click my header and select Drill Down. And I’ll rename this query as well (ListDescr2).
I’ll then step back to my Result query, select Description2 column, and pressed 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 by the list we’ve just created. And now, as you can see, this also picked up column 8 that we previously excluded.
In this blog, I showed you how to dynamically merge columns in Power Query using M codes. It’s not difficult at all 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 checkout the links below for more related content.
All the best!
***** Related Support Forum Posts *****
Merge Dynamic Number of Columns
Top Customer Salesperson with Power Query
Combine, Merge, and Append
For more merge columns queries to review see here….