How to Use Concatenate in Power Query

Power Query in Excel is a great tool to use in data preparation and when you need to concatenate or combine, values from different columns or rows even files. In this article, we’ll dive into how you can use Power Query to concatenate values.

What is Concatenation?

Concatenation refers to the process of joining two or more values together. For example, if you have a first name in one column and a last name in another column, you might want to concatenate them to have a full name in a single column.

Steps to Concatenate in Power Query:

1. Load Your Data into Power Query:
   • Go to the ‘Data’ tab in Excel.
   • Choose ‘Get Data’ and select your data source.
   • Click ‘Edit’ to open Power Query Editor.
2. Select the Columns You Want to Concatenate:
   • Click on the first column you want to concatenate.
   • Hold the ‘CTRL’ key and click on the other columns you want to concatenate.
3. Use the Concatenate Option:
   • Right-click on one of the highlighted columns.
   • Choose the ‘Merge Columns’ option.
   • In the ‘Separator’ drop-down, select the character you want to use to separate the values (e.g., space, comma, etc.).
   • Enter a new name for the concatenated column.
   • Click ‘OK’.

Right Click

Chose how you want to separate the column by indicating which separator you desire and the name of the concatenated column

Use M Code to Concatenate in Excel

1. Advanced Concatenation with M code: If you want more control over the concatenation process or need to perform more complex concatenations, you can use M code in Power Query. For example, to concatenate a first name and last name with a space in between: Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName])
2. Load the Transformed Data Back to Excel:
   • Once you’ve finished concatenating your columns, click on the ‘Close & Load’ button in the Power Query Editor to load your transformed data back into Excel.