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.
- Concatenate only text columns
- Concatenate text and numeric columns
- Combine text and date columns
- 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.
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.
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.
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.
= [SalesOrderNumber]&"-"&[FullName]
The output of this simple new column is as seen in the diagram below.
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.
But as you would see the above code would generate errors as output in the table as seen in the table below.
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.
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.
= [SalesOrderNumber]&"-"&Number.ToText([CustomerKey])
The output of the above M Query code in the table is as seen below.
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.
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.
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.
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.
= [SalesOrderNumber]&"-"&Date.ToText([OrderDate])
The output of the new column would be as shown in the table below.
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.
= [SalesOrderNumber]&"-"&DateTime.ToText([OrderDateTime])
The output of the new column should look like the column in the table below.
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.
