Concatenate Strings in Power BI Using Power Query M Language

By:   |   Updated: 2022-01-05   |   Comments   |   Related: > Power BI


Problem

Recently, I wrote about concatenating strings in Microsoft Power BI using DAX. There might also be reasons to concatenate strings within the Power Query Editor using M language. I came across such a requirement recently when I needed to create a column in Power Query (rather than in Power BI DAX) to be used for modelling purposes. While I worked on the task I ran into some minor issues. Although I did resolve these issues later, but I thought this was a good idea to write a brief tutorial on to help others that might face similar requirements and challenges while trying to concatenate strings in Power Query.

Solution

Besides just choosing between whether to create this concatenated column in DAX or Power Query, there might be other reasons to choose to do so in Power Query rather than with DAX functions, including model performance purposes.

I have divided this tutorial into four sections, each demonstrating how to use M language in Power Query to combine strings in several ways. The sections are as listed below.

  1. Concatenate only text columns
  2. Concatenate text and numeric columns
  3. Combine text and date columns
  4. Combine text and datetime columns

I will be using the sample dataset shown below for the purpose of this demo. It is expected that your datasets would surely be more complicated or different, but the approach is the same.

Sample dataset to demo concatenation in Power Query M language

Concatenate Only Text Columns

This is relatively the easiest to achieve in all the concatenation types being that we are combining columns with same data types, in this case Text data types.

To do this, while within the Power Query Editor, click on the "Add Column" tab and then select "Custom Column" as seen in the diagram below.

Create new columns using Custom Column in Power Query.

Now, let's create a new column by combining the two text columns, "FullName" and "SalesOrderNumber". Once you click on the Custom Column selection on the Add Column tab the below window should open.

Custom Column window in Power Query

You can choose to change the "New column name" from "Custom" to something else. We will change it to "TextCombine" as a column reference for this tutorial.

Next, we need to enter an M query that returns the column in the way we want it. In this case we will start with the "SalesOrderNumber" and then the "FullName" by joining them with a "- "as seen below. All you need do is either double click on the columns you need to use in the order you need and add the concatenating symbol as highlighted in the diagram and M code syntax below.

Naming and concatenating columns in Custom Column.
= [SalesOrderNumber]&"-"&[FullName]

The output of this simple new column is as seen in the diagram below.

Output of concatenated text columns on table.

Note that you can add as many column combinations as you require just following same logic, or you can use any other delimiters like commas, spaces, forward or backward slashes, etc to join the columns as you require.

Concatenate Text and Numeric Columns

This is where it starts to get a bit more challenging as would become clear in the demo. This requires the combination of text and numeric data types only. Let's try combining the "SalesOrderNumber" and the "CustomerKey" columns.

To do this, still within the Power Query Editor, click on the "Add Column" tab and then select "Custom Column" as done earlier.

This time we will name the new column as "TextNumericCombine" as seen in the diagram below.

Naming and concatenating columns in Custom Column 2.

But as you would see the above code would generate errors as output in the table as seen in the table below.

Error output of concatenated text & numeric columns on table.

The reason is that we cannot use the ampersand (&) symbol in combining a text column with a numeric column. You can also get this reason for this by clicking on the error value in the table, and a message would show as seen below.

Error message for text and numeric concatenation.

So, how do we solve this? To be brief, you need to use the M code in the next diagram below, which along with the ampersand (&) symbol we are adding an M Query function (Number.ToText()) to convert the numeric value to text value before combining.

Naming and concatenating columns in Custom Column 3.
= [SalesOrderNumber]&"-"&Number.ToText([CustomerKey])

The output of the above M Query code in the table is as seen below.

Output of concatenated text & numeric columns on table.

Combine Text and Date Columns

This requires a combination of text and date columns with text and date data types. For this, lets try combining the "SalesOrderNumber" and "OrderDate" columns.

To do this, while still within the Power Query Editor, click on the "Add Column" tab and then select "Custom Column" like how we did it earlier.

Let's name the new column as "TextDateCombine" as seen in the diagram below.

Naming and concatenating columns in Custom Column 4.

Again, we will need to include an M Query function to make this work too (like in the case of combining text and numeric values) as the above M Query code would result in an error too as seen in the table below.

Error output of concatenated text & date columns on table.

Reason for this error is like that of combining text and numeric data columns, and if you click on an error value you should see the message as below.

Error message for text and date concatenation.

To solve this, we need to use the function "Date.ToText()" to convert the date column to text first as seen in the diagram and M code below.

Naming and concatenating columns in Custom Column 5.
= [SalesOrderNumber]&"-"&Date.ToText([OrderDate])

The output of the new column would be as shown in the table below.

Output of concatenated text & date columns on table.

Combine Text and Datetime Columns

This is very similar to combining text and date columns. Only difference is that in this case we are combining Text and DateTime column data types. Thus, we require a separate M Query function to achieve this as we will return errors again if we use the Date.ToText() function for a column with DataTime data types.

In this case, to solve this, we need to use the "DateTime.ToText()" function which should ensure the DateTime column is converted to text before combining to the text column as seen in the diagram and M Query code below.

Naming and concatenating columns in Custom Column 6.
= [SalesOrderNumber]&"-"&DateTime.ToText([OrderDateTime])

The output of the new column should look like the column in the table below.

Output of concatenated text & datetime columns on table.

In summary, note that you can do this concatenation in any combination like numeric and numeric, date and datetime, or datetime and numeric, all follow same approach. It could have also been Decimal data type combination with other data types, in this case the decimal is considered as numeric data type and you can use the Number.ToText() function as used for the numeric data types too.

Next Steps
  • Read more about the Microsoft Documentation on M Query Type conversion here.
  • You can get more information on using the Number.ToText M Query Function here.
  • You can get more information on using the Date.ToText M Query Function here.
  • You can get more information on using the DateTime.ToText M Query Function here.
  • Try this tip out with your own data as the business requires.





get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Microsoft Certified Data Analytics and BI Professional mostly in Microsoft BI stack of tools.

View all my tips


Article Last Updated: 2022-01-05

Comments For This Article

















get free sql tips
agree to terms