How to Use Power Query Concatenate Function

Sometimes, you may want to perform tasks that aren’t available in the Power Query user interface, such as concatenating data. This can be achieved using Power Query’s programming language, M language. In this article, we’ll learn how to concatenate data or text in Power Query using M language.

Method 1: Power Query Concatenate Data/Text using M Language

We’ll start with a simple example of merging first and last names into a new column using the Power Query CONCATENATE formula.

Steps to Concatenate Data in Power Query (Excel):

1. Load Data in Microsoft Power Query Excel:

• Load your data into Power Query.
• Check the data you want to concatenate.
• Use Power Query to concatenate the Name and Surname columns.

1. Add a Custom Column in Power Query Excel:

• Click on the Add Column tab.
• Select Custom Column from the ribbon.

1. Write a Custom Column Formula using M Language:

• Use the formula [Name]&” ”&[Surname] to concatenate the columns.
• The ampersand (&) combines the values, and the double quotation marks add a space between them.

1. Close & Apply:

• Click on Close.
• Click on Apply.

A concatenated column will be added to your data table.

Concatenate in Power BI:

1. Load Data in Power BI:

• Load your data into Power BI.

1. Add a Column:

• Click on Add Column from the ribbon.
• Write the formula Concatenated Column = TableName[firstname] & ” ” & TableName[lastname].
• Click on Close & Apply.

You will now have a concatenated column in Power BI.

Method 2: Power Query Concatenate Columns using Merge Columns Feature

1. Load Data in Microsoft Power Query Excel:

• Select your raw data.
• Go to Data > From Table/Range.
• Load your data into Power Query.

1. Merge Columns:

• Select the columns [First Name] and [Last Name].
• Go to the Add Column tab and select Merge Columns under the From Text group.
• Choose a separator (e.g., whitespace).
• Give the new column a name.
• Click on OK.

You will get a new column named [Full Name]. Note that using the Merge Columns option under the Add Column tab does not overwrite existing columns. To overwrite, use the Merge Columns option under the Transform tab.

Method 3: Concatenate Columns using Column From Example

Column From Example allows you to create a new column by specifying a pattern.

1. Load Data in Excel’s Power Query:

• Select your raw data.
• Go to Data > From Table/Range.
• Load your data into Power Query.
• Select the columns [First Name] and [Last Name].

1. Create Column From Example:

• Go to the Add Column tab.
• Select Column From Examples under the General group.
• Choose From Selection from the dropdown.
• Write the desired output pattern.
• Click OK or press Ctrl+Enter when satisfied with the preview result.

You will get a new column named [Merged].

Summary:

This guide provides three methods to concatenate data or text in Power Query. Method 1 uses M language to write a custom formula. Method 2 uses the Merge Columns feature for a more straightforward approach. Method 3 leverages the Column From Example option to create a new column based on a pattern. Each method offers a different way to achieve the same goal, catering to various user preferences and requirements.