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.
Concatenate Datausing ‘M’ With Power Query
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 CONCATENATE formula. However, we will use easy steps for you to understand how to use ‘M’ in Power Query.
Step 1: Load data in 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 concatenate the Name and Surname columns together.
Step 2: Add Custom Column
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
Step 3: Write custom column formula
To concatenate two columns in Power Query we have to write a formula in the formula box as given below
The Ampersand (&) will combine the values together. To add space in the middle, double quotes are used.
Step 4: Close & Apply
Click on Close and Apply button after you get the concatenated column added to your data table as shown below.
You can learn more :
- Replicating Excel’s Find Function With ‘M’ in Excel
- Get The Length Of Strings Using ‘M’ in Excel
- Best Ways to Add Column From Examples in Power Query
- Read How to Add Conditional Column in Power Query