Power Query Concatenate Values: A Quick Guide to Merging Data in Excel

I’ve been delving into Power Query recently and found it to be a versatile tool in Excel for managing and transforming data. It’s particularly handy when you need to consolidate information, like piecing together first and last names into a full name, or constructing a complete address from its individual components. Power Query, or the Query Editor as it’s sometimes known, provides a range of methods for this exact purpose—concatenating text and numbers.

The process starts right in the Data tab within Excel, where you initiate Power Query through the Get Data option. From there, you get to choose from an array of files or data sources to load your data. Once your data is imported into the Power Query Editor, you’re greeted with a comprehensive interface that lists a series of applied steps for data preparation. It’s such a relief when dealing with raw data that needs cleaning or reformatting, and you get to control each transformative step meticulously, ensuring your dataset is primed for analysis or reporting.

Concatenate Values from Two Columns into One (Combine Columns)

Merging data from two columns into a single one is a breeze with Power Query in Excel. Here’s how I get it done:

Load the data into Power Query Editor.
Select the columns you want to combine, like Column1 and Column2. These might be first and last names, for example.
I go to Transform and choose Merge Columns.
In the dialog box, I pick my separator to place between the values – space, comma, or anything else – and give my new column a name.
Hitting OK does the trick. And just like that, I’ve got a new column with combined values.

Remember to line up the columns in the order you want them merged. With a custom separator, your data will look exactly how you need it. Now your rows are clean and orderly, just how they should be!

Concatenate Columns by Adding a New Column

I often find that I need to merge data from two columns into one to streamline my tables. It’s pretty easy, let me show you how. I punch in a new name for my column and then roll out a simple formula. To add some clarity, here’s what I do:

First, I get to the Add Column tab, and I hit Custom Column. That opens up a dialog box where the magic happens.
Here’s what I type in with my new column’s name at the top: =[First] & " " & [Last]

This M-code piece merges the ‘First’ and ‘Last’ columns and places a space between the combined values. Now, isn’t that handy? It’s just like the formulas we whip up in Excel, except this one’s for Power Query.

I wrap up by clicking OK, and voilà, a fresh, new combined column pops up in my table.

And that’s how I get to concatenate without breaking a sweat!

Combining Text and Number into One Column

When I need to merge texts and numbers into one column in Power Query, I avoid errors by converting numbers into text first. Here’s how I do it:

Use the Number.ToText function to change numbers to text format.
Concatenate the text and converted numbers with an ampersand (&) or a custom separator, like a comma.
Carefully inspect the data to ensure no null values, as this might interrupt the process.

For instance, to join a name and an ID number, I’d use:

= Table.AddColumn(PreviousStep, "Combined", each [Name] & ", " & Number.ToText([ID]))

This code adds a new column where the name and ID number are separated by a comma.

Combining Values from a Single Column

When I need to condense multiple values into a single cell, I start by selecting the desired column and hitting “Transpose”. This flips the table, turning the column I need into a row.