Excel Power Query supports multiple ways to combine columns, you can concatenate them using custom formulas, utilize the merge feature, or create a custom column from an example of the desired final output leveraging AI to generate formulas for you.
Power Query is one of the most useful features of Microsoft Excel. It enables users to work with large datasets, automate processes, and ultimately save time compared to working within the standard Excel interface, but there is a learning curve to figure out how to best utilize it.
Let’s jump in!
Table of Contents
Launching the Excel Power Query Editor
The first step to combining columns in Excel, is launching the Power Query Editor. You can either edit an existing Power Query, Get Data from an external source, or from data that’s already on a sheet of your workbook. To start with new data, go to the Data section of the Excel Ribbon and select the desired method for importing data.
After data is selected, the Power Query Editor will pop up in a separate window. This is the area that you will work with your data.
The different options for working with and transforming data are show on the Power Query ribbon at the top of the page, and many functions can also be quickly accessed by right clicking on the column names of your dataset.
After each transformation, the steps are recorded on the right side of the screen in the Applied Steps section. These can later be re-ordered, edited, or deleted as necessary.
When working in Power Query, there are a couple of items to keep in mind that differ from working with regular Excel worksheets.
Differences When Working in Excel Power Query
The biggest difference when working in Power Query is that data has to be adjusted on a column-by-column basis. Formulas and steps are applied to the full column of data and filled down for each row. Formulas use the Power Query M language and differ from the standard Excel Syntax.
When creating formulas, columns are referenced by column names surrounded by square brackets, and many of the common formulas such as VLOOKUP and SUMIF do not exist, but there are methods of re-creating a similar result.
How to Concatenate Columns in Excel Power Query
To concatenate columns in Power Query, launch the Power Query editor and navigate to the Add Columns section of the Ribbon. Click on Custom Column to launch the Power Query M Formula Editor. Columns are concatenated by referencing the column name surrounded by square brackets and concatenated with an & symbol.
Let’s take a look in more detail.
The screenshot below shows a Custom Column being created using a Custom Column. Once you select this button a formula editor will pop up.
Note: Formulas in Power Query are typically entered this way instead of typing them in a formula bar like you would in Excel. However, it is possible to do so.
In this example, we are combining the values in the City and State columns.
Tips for Working with Power Query Formulas
Power Query formulas work differently from Excel formulas. Here are some tips that will help you get started.
- There is no need to type in an = sign at the beginning of a formula.
- Columns are referenced by Column Name
- Column Names are surrounded by square brackets
- You can double click Available Columns on the right to auto insert a column into a formula
- Text must be surrounded by quotes
- Numbers do not need to have quotes around them
Now that we have the basics out of the way, here’s how to concatenate.
The syntax to concatenate in Power Query is the same as it is in a regular Excel formula and uses the following syntax.
New Column = [Column1]&[Column2]
Using this method will combine the contents of each column.
Tip: If you run into an error regarding data types, you will need to right click on the column header in the Power Query editor and make sure that the data types are set to text and match between the multiple columns being concatenated.
Concatenate Columns with Seperators in Power Query
To concatenate columns with separators in Power Query, add a new custom column. In the formula editor box, combine the column names surrounded by square brackets with an & symbol with the desired text in the middle surrounded by quotation marks.
The formula will look like the following.
New Column = [Column1] & " - " & [Column2]
Column1 is concatenated with a space, hyphen, and space, then concatenated with Column2.
You are able to combine multiple columns using this method.
How to Merge Columns in Excel Power Query
To merge columns in Power Query, first launch the Power Query Editor. Then navigate to the Add Column section of the Power Query Ribbon. Select the columns you want to combine, and the Merge Columns button will activate. Press the button and you will see a popup to configure the merge criteria.
Here’s how in more detail.
Navigate to Add Column, and select multiple columns to Merge.
Tip: You can select multiple columns by clicking on the column name while holding down CTRL, or you can hold down SHIFT to select a range of columns.
Prior to selecting multiple columns, the merge columns button will be greyed out and inactive. It only activates once columns to merge are selected.
The Merge Columns settings will appear giving you the option to define a specific separator, such as a comma, or you can use a custom separator if you want to combine columns using multiple characters like a comma followed by a space.
Press OK and the new column will appear to the right of your dataset.
Combine Columns in Excel Power Query Using AI Examples
Creating Columns from Examples is an extremely useful feature of Power Query. It works by allowing you to provide examples of desired output, and Power Query will automatically fill the column down and suggest a formula that can be accepted and applied.
To start a Column From Example, navigate to Add Column, Column From Example in the Power Query Ribbon.
In the screenshot below we chose to work with selected columns and the add column from examples interface begins.
The feature is configured by typing the desired column output into the blank column on the far right. Power Query will automatically suggest different formulas, and provide a preview of the values that will be filled in. At the same time the formula being generated is shown above the column names of the dataset.
Columns from Example do not always work on the first try, if you don’t immediately get the results you were hoping for try adding additional rows of data to give Excel more data points to base its formula off of.
In our experience the feature works extremely well for common tasks but can struggle when trying to get it to generate more complex formulas.
Concatenating and merging columns in Power Query for Excel can be done using multiple methods ranging from creating custom formulas similar to what you would in a regular Excel worksheet, with the primary difference being that columns are referenced by name instead of by letter. Options also exist to multi-select columns to activate the Merge button, or you can provide examples of desired output and let Power Query generate the formulas for you by working backwards from provided examples.