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.
In this article, we going to learn how you can concatenate Data/Text in Power query with help of ‘M’ Language.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
Method 1: 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 are the 4 easy steps:
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
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
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
Step 4: Close & Apply
- Click on Close
- Click on Apply
A concatenated column gets added to your data table.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
How do you Concatenate in Power BI
To concatenate in Power BI, you should follow the below steps:
- First Load Data in Power BI to concatenate
- Click on Add Column from ribbon
- Write Syntax Concatenated Column = TableName[firstname] & ” ” & TableName[lastname]
- Click on Close & Apply
- Now, you will get Concatenate in Power BI
Method 2: Power Query Concatenate Columns using Merge Columns Feature
Step 1: Load data in Microsoft Power Query Excel
- Select the raw data Go to Data > From Table/Range
- Load data in Power Query
- Check the data you want to concatenate in this example [First Name] and [Last Name]
Note: To practice with us. We have shared the Excel sheet. You can download it from the link below
Step 2: First Select Column [First Name] then [Last Name]
- Go to Add Column Tab > Merge Columns under “From Text Group”
- Select the Separator from the dropdown as “ “ (whitespace)
- Give new column Name
- Click on “ok”
Here you go…
You will get a new column as [Full Name]
Note:- By using the Merge Columns option under Add Column Tab we’re not overwriting our existing columns. If you wish to override your existing columns by doing merge columns then use the Merge Columns option under Transform Tab.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
Mehtod 3: Now we will learn about concatenating two columns using the “Column From Example” option.
COLUMN FROM EXAMPLE –
Column From Example helps to create a new column in the existing table by specifying a pattern.
The Column From Example has two options:
- From All Columns option forces Power Query to find out a given pattern from all columns in the table.
- From Selection option forces Power Query to find out a given pattern from selected columns only.
Step 1: Load data in Excel’s Power Query
- Select the raw data Go to Data > From Table/Range
- Load data in Power Query
- Select the data you want to concatenate in this example [First Name] and [Last Name]
- Go to “Add Column” Tab > “Column From Examples” under General group > Click on the dropdown and select option “From Selection”
Step 2: Load data in Excel’s Power Query
- Select the raw data Go to Data > From Table/Range
- Load data in Power Query
- Select the data you want to concatenate in this example [First Name] and [Last Name]
- Go to Add Column Tab > Column From Examples > Click on the dropdown and select From Selection
- Try to write the output result you want from the selected columns
- Once you are satisfied with the Power Query’s preview result click on “Ok” or press Ctrl+Enter
Here you go…
You will get a new column as [Merged]
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
Conclusion:
Overview: Here are the basic steps to Concatenate Data using ‘M’ in Query
- Load data in Power Query
- Add Custom Column
- Write custom column formula
- Now, Close and Apply