Combining Columns in Power Query can be done a number of ways. You can use the Merge Columns feature, formulas to concatenate, and you can combine columns using the column from example feature that uses AI to generate Power Query formulas on your behalf.
If you’ve concatenated columns in Excel, the formula method may be the most familiar but there are some nuances, such as how you refer to columns by name instead of letters and creating columns instead of entering formulas. They’re subtle differences, but once you get the hang of it can be a big time saver.
Let’s jump in!
Table of Contents
Understanding Power Query
Power Query, a versatile tool in Microsoft Excel, Power BI, Data Flows and Data Wrangler, for streamlining repetitive data transformation tasks. It allows users to easily import, clean, and consolidate data from a wide range of sources. You can connect to live data like SQL databases, Excel Spreadsheets, folders full of files etc.
If you’re coming from the standard Excel spreadsheet world, Power Query represents a new way of working with data. Data is transformed at the table and column level, meaning that any operation you perform will generally involve creating a new column or updating a full column that already exists rather than making adjustments cell by cell. Power Query also uses the M formula language, which differs from standard Excel formulas.
Combining Columns in Power Query
Combining columns in Power Query can be completed by holding CTRL + Left-Clicking on multiple columns to highlight them. Once highlighted, click the “:Merge Columns” button from the “Add Column” section of the Power Query Ribbon, select a seperator, and a new column will be generated.
Here’s how in more detail:
Step-by-Step Guide to Combine Columns
- Open Power Query Editor: Initiate by loading your data into Power Query.
- Select Columns: Click on the columns you wish to combine while holding the ‘CTRL’ key.
- Combine Columns: Right-click on one of the selected columns, choose “Merge Columns” from the context menu, specify a separator if needed, and give the new column a name.
The example below shows two columns being selected to combine First Name and Last Name.
The Merge Columns dialog box will appear asking for a separator to be defined. We’ll use a space but you can also select common delimiters such as a comma to combine columns but allow you to split them apart easily in the future.
You can also name the new combined column in this step. Press OK and a new column will be generated to the right of the original data columns.
Concatenating Columns in Power Query
Concatenation in Power Query is a technique used to merge the contents of two or more columns into one, with the option of inserting a separator between the merged values using formulas. Columns are defined by column name surrounded by square brackets and concatenated with an ampersand symbol.
Here’s how in more detail:
Step-by-Step Guide to Concatenate Columns
- Prepare Your Data: Ensure your data is loaded into Power Query.
- Use the ‘Add Column’ Tab: Navigate to the ‘Add Column’ tab and select ‘Custom Column’.
- Concatenate Using M Code: In the custom column formula, use the ‘&’ operator to concatenate the columns. For example,
[Column1] & " " & [Column2]
You’ll notice in the example below that columns are referenced by column name surrounded by square brackets. The & symbol combines a space which is surrounded by quotes to designate that the characters between the quotes are to be interpreted as text.
The result of the concatenate formula is a new column called Full Name that combines the First Name a Space and the Last Name column.
Merge Power Query Columns from Example
The Column from Example feature of Power Query uses AI to suggest Power Query M formulas for new columns based on detected patterns. Go to “Add Column” and select “Column From Examples” Enter the desired outcome as an example in the far right column. It will suggest and auto-complete with recommended formulas.
The Power Query M formula recommendation is shown at the top of the editor. In this case, It’s suggesting use of the Text.Combine Power Query Formula to generate a new custom column.
The Column From Examples feature works best with simple transformations, but can be surprisingly useful. If you don’t get the correct recommendation at first, try filling out additional rows to give Power Query examples of what the desired outcome should be.
Larger sample sizes of end results can help Power Query determine the correct pattern needed for a formula.
Merging All Columns in Power Query
To Merge All Columns in Power Query into a Single Column, launch the Power Query Editor, select all columns (CTRL + A) then press the “Merge Columns” button from the “Add Columns” section of the Power Query Ribbon. Select a separator and name the new column. Press OK to generate a new column with combined values from all other columns.
Once combined, you can choose to remove all other columns to get ar esult of a single column with deliminters.
Tips for Combining Columns and Concatenating
To avoid errors when combining columns in Power Query, there are a few things that you should keep in mind:
- Data Types Matter – Ensure the data types of the columns being combined are text. Power Query will return errors when trying to concatenate numeric columns with text columns. Right click a column header to change the data type prior to merging it.
- Using Functions for Complex Scenarios – Consider M language functions like
Text.Combinefor more complex concatenation needs.
- Cleaning Data Beforehand – Clean and trim your data before combining or concatenating to avoid unexpected results.
Combining columns in Power Query can be quick and efficient, when you’re done click Close & Load which will finalize the changes.
Power Query provides a number of different methods to merge columns and concatenate. The most common is using an & symbol in-between column names surrounded by square brackets, but you can also use the “Merge Columns” button or columns from example when working with multiple columns or more complex data sets.
Power Query was first introduced in Microsoft Excel, but the functionality is equally important or arguably more so in Power BI. Microsoft has also made Power Query available in the cloud through Microsoft Fabric Data Flows and Data Wrangler, a data prep tool for Machine Learning. It’s as relevant today as it was when introduced back in 2010.