5 Easy Steps To Concatenate Data Using 'M' In Power Query

Home » Colonne Personnalisée Power Bi Concatenate » 5 Easy Steps To Concatenate Data Using 'M' In Power Query

Maybe your like

Power BITutorials Learn 3 different ways to Merge Columns in Power Query By excelsuperstar20 May 2, 2020 3 Mins read 124228 20 Share

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)

GET POWER BI GUIDE

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

Step 4: Close & Apply

  • Click on Close
  • Click on Apply

A concatenated column gets added to your data table.

AFTER CONCATENATING COLUMNS USING M

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)

GET POWER BI GUIDE

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

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

Click here…

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)

GET POWER BI GUIDE

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)

GET POWER BI GUIDE

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
124228 20 Share
Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

By excelsuperstar20 September 17, 2021 2 Mins read Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows… AlteryxTutorials

Step By Step Guide to Learn Alteryx

By excelsuperstar20 September 14, 2021 6 Mins read Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the… Excel VBA CodeTutorials

VBA Code to Clean the Date Format

By excelsuperstar20 February 13, 2021 1 Mins read When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other… Analyze & Visualize data using Power BI

You too can analyze data like a Data Scientist. No coding needed. No statistics needed.

  • Understand Power BI and Power Query Editor’s UI
  • Essential Charts to create Power BI Dashboard
  • Bonus on the last page of the eBook.
Get Power BI eBook x

Tag » Colonne Personnalisée Power Bi Concatenate