fbpx

5 Easy Steps to Concatenate Data using ‘M’ in Power Query

How to use power query concatenate function? There are times when we want to perform such tasks that are not built in the user interface i.e. features that are not available on the ribbon. But still, this can be possible with Power Query’s programming language which is called M language.

Automation using Power Query for Excel Users banner

Power Query Concatenate Data/Text using ‘M’ Language

Initially, we will do a simple example of merging the first name and the last name into a new column. This can be done with the help of the Power Query CONCATENATE formula. Check out Power Query Top 10 Tips & Tricks. However, we will use easy steps for you to understand how to use ‘M’ in Power Query.

Before going into details, let’s see the list of steps that are used for concatenate data using a power query in excel. Here is the list of detail steps:

  1. Load data in Microsoft Power Query Excel and Type =CONCATENATE
  2. Add Custom Column in Power Query on each cell you want to concatenate.
  3. Write a custom column formula using M Language
  4. Close & Apply

Now let’s discussed each step in detail.

Step 1: Load data in Microsoft Power Query Excel

  • Load data in Power Query
  • Check the data you want to concatenate
  • Use Power Query to Concatenate Name and Surname column together

BEFORE CONCATENATING COLUMNS USING M

Step 2: Add Custom Column in Power Query Excel

The custom Column feature allows writing a query in M Language.

  • To add Custom Column, Click on Add Column
  • Select Custom Column from the ribbon

ADD CUSTOM COLUMN

Step 3: Write a custom column formula using M Language

To concatenate two columns in Power Query, you have to write a formula.

  • Syntax of Formula is [Name]&” ”&[Surname]
  • The ampersand (&) combines the values together
  • (“ “) Double Inverted comma adds space in the middle

CONCATENATE COLUMNS USING M LANGUAGE

Step 4: Close & Apply

  • Click on Close
  • Click on Apply

A concatenated column gets added to your data table.

AFTER CONCATENATING COLUMNS USING M

Excel & Power BI Training

How do you Concatenate in Power BI

To concatenate in Power BI, you should follow the below steps:

  1. First Load Data in Power BI to concatenate
  2. Click on Add Column from ribbon
  3. Write Syntax Concatenated Column = TableName[firstname] & ” ” & TableName[lastname]
  4. Click on Close & Apply
  5. Now, you will get Concatenate in Power BI

Watch Video & know use Cases of Power Query

You can learn more :

Conclusion:

Overview: Here are the basic steps to Concatenate Data using ‘M’ in Query

  1. Load data in Power Query
  2. Add Custom Column
  3. Write custom column formula
  4. Now, Close and Apply

If you wish to learn Power Query and build your career in Data Cleaning and Data Transformation, then Check our Power Query Training Course that comes with expert live training and live projects.