The August update for Power Query was finally made available on Sept 1, and it has some pretty cool stuff in it. In this week’s segment I thought we’d cover off one of the features that I’m most excited about as an Excel Pro: Merging columns with Power Query.
The old way
It’s been possible to merge two or more columns together in the past, but you had to write a formula to do it. Honestly, it wasn’t a huge deal, but it still took a bit of know-how and work. Assume, for example, we had this:
And our goal is to concatenate the Account and Dept columns together with a hyphen between them. Here’s what you had to do:
- Insert a New Column (the steps for this varied depending on the version of Power Query you are running. Currently it is Add Column –> Add Custom Column)
- When the prompt pops up you had to provide a formula like shown below:
Okay, so not a huge deal. Just =[Column1] & “-“ & [Column2]
But you still had to write it. I’ve lost count of how many people to whom I’ve taught the simple & shortcut for Excel formulas, but it’s enough to say that it’s probably not intuitive.
So it worked, but could it become easier? We now know the answer is Hell Yeah!
The new way
This time we’ll do it differently…
- Select the Account column
- Hold down CTRL (or SHFT) and select the Dept column
- On the Add Column tab, click Merge Columns
- Choose your separator. The default is –None- (meaning it will just mash them together), but other pre-defined options include Comma, Colon, Equals Sign, Semicolon, Space, Tab
- What I want (a minus sign) isn’t there, so I’m going to choose --Custom--
- Now I’ll enter a - (minus) sign and click OK
And that’s it! My output comes together nicely:
Now, to be fair, I still have to rename the column. I do wish this interface had a way to name the column in advance (like exists when you create a custom column.) Hopefully the PQ team will retrofit us with that ability at some point in the future.
In the mean time, we can either right click the column header and rename it there, or we can edit the column directly in the formula bar. Just change the highlighted part shown below:
Like this:
So honestly, it’s not that much more efficient, why do I think this is cool? Well, it’s not that much more efficient with 2 columns. But try 4. Or when you just need to put 4 columns back together with no spaces in between. Then it starts to make life much easier.
11 thoughts on “Merging Columns with Power Query”
Pingback: Excel Roundup 20140915 « Contextures Blog
Nice tutorial, thanks Ken!
Amazing work Ken! Your work is a great guideline.
I have a weird question I'd like to ask. Do you think it is possible to merge all the columns of a table in groups of two automatically? For instance a 100 column table will be reduced to 50 columns by merging 2 subsequent columns.
Thanks in advance
-HB
I think it could be done. It would take some funky functions, but yes.
Hey Ken,
While I love this method, I'm struggling to find a way to handle merging 8 columns where some of the columns will contain blanks (or nulls).
Is something like this even possible easily, or would it require something really funky?
Thanks in advance!
--Sean
Hey Sean,
The question comes down to what you want to happen with the nulls. If you want to have them suppressed, use the Merge Columns from the Transform or right click menus. If you want to keep the nulls in place (so that you end up with delimiter-null-delimiter), then use Add Column --> Merge Columns. They behave a bit differently in that the latter keeps the nulls in place.
Hope that helps!
Hi Ken, thanks for the instructive blog. I am combining RSS feeds from Reuters, and there's about 12 if them (US, World, Art, Sci, etc).
I appended them into a giant table that maintained a common Date & Link column, but the Headline column caused them to spread out with a ton of nulls in a diagonal shape.
After using merge (with no separator) this is mostly fixed, but I'm wondering if it's possible to still retain some kind of mid-column headers for each of the branches to allow for individual manipulation.
Granted it is still possible to tell which is which by looking at the Link column, but it isn't optimal.
Thanks!
Hi Scott,
I'd suggest asking about this in our forums at https://www.excelguru.ca/forums as it would be easier to diagnose this with a visual sample. 🙂
Thanks for the tip.
I want to merge 2 columns, if one of them has null value.
Is there a way?
Hi Ozgur,
I think you actually need a conditional column in this case if I understand what you're trying to do. Basically it would look like this:
=if [Column1] = null then [Column2] else [Column1]
Pingback: Name Columns During a Merge