fbpx
[email protected] +1 203-349-9909 / +91-8080042523

5 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.

Power Query Concatenate Data 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 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.

Step 1: Load data in Microsoft Power Query

Load the data in Power Query and check the data that you want to concatenate. As shown in the below picture, we are going to use Power query concatenate the Name and Surname columns together.

BEFORE CONCATENATING COLUMNS USING M

BECOME A BUSINESS INTELLIGENCE ANALYST COURSE
• Power BI • Excel to Alteryx • SQL Programming Language • SAS Programming • Tableau • KNIME • many more

 

Step 2: Add Custom Column in Power Query

Custom Column feature allows writing a query in M language. Therefore, we have to use a Custom Column feature here. Go to-

Add Column  >  Custom Column

ADD CUSTOM COLUMN

Step 3: Write a custom column formula using M Language

To concatenate two columns in Power Query we have to write a formula in the formula box as given below

[Name]&” “&[Surname]

The Ampersand (&) will combine the values together. To add space in the middle, double quotes are used.

CONCATENATE COLUMNS USING M LANGUAGE

Step 4: Close & Apply

Click on Close and Apply button after you can see power query combine columns and get the concatenated column added to your data table as shown below.

AFTER CONCATENATING COLUMNS USING M

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