During analysis, you can concatenate text and numbers in Power Query by using the “&” operator or the Text.Combine function in Power BI.
This can be due to creating more informative column labels, creating new composite keys, calculated fields, etc., and can help make data more meaningful, informative, and useful for data analysis and decision-making.
In this article, I will walk you through detailed steps on how to concatenate text and numbers in Power Query.
Let’s get started.
Introduction: How to Concatenate Text and Numbers in Power Query
You can use both the Text.Combine function and the “&” operator to concatenate text and numbers in Power Query, however, they work in slightly different ways. I will explore both methods:
Using the “&” Operator
The first thing to do is to import the data into Power Query. To do that, click on the Get Data button on the Home tab and select the data source such as Excel, CSV, SQL Server, or other data sources you want to import.
Add a custom column
In the Power Query Editor, select the column that contains the text and click on the Add Column tab. Then click on the Custom Column button. Then give a name to the new column that you want to create.
Enter the formula
Imagine you have a table with two columns: Name and Age, and you want to create a new column that concatenates the two values. You can enter the following formula in the dialog box:
New_Column = [Name] & " is " & Text.From([Age]) & " years old."Replace Name with the name of the column that contains the text, and Age with the name of the column that contains the numbers. Then click on the OK button to create the new column.
The “&” operator is used to concatenate the values and the Text.From function is used to convert the number to text.
This process will create a new column that will contain the concatenated values of the Name and Age columns, separated by the text “is” and the string “years old.”
For example, if the Name column contains John and the Age column contains 35, the new column will contain the value John is 35 years old.
Load the data into Power BI
Finally, click on the Close & Load button in the Home tab to load the data into Power BI.
Using the Text.Combine Function
To concatenate text and numbers using the Text.Combine function, you must load the data into Power Query, and select the columns that you want to combine, including the column with the numbers.
Go to the Transform tab and select Data Type >> Text to convert the numeric column to a text column.
After this, select the columns that you want to combine go to the Add Column tab, then click on the Custom Column button.
In the dialog box, enter the following formula:
New_Column = Text.Combine({[Name], Text.From([Age]), [Gender]}, " ")This formula will create concatenated values of the Name, Age, and Gender columns and separate them with a space.
For example, if the Name column contains John, the Age column contains 50, and the Gender column contains Male, the new column will contain the value John 50 Male.
Finally, click the OK button to create the new column, and load the data into Power BI by clicking Close and Apply.
Conclusion: How to Concatenate Text and Numbers in Power Query
That’s it! You have successfully concatenated text and numbers in Power Query.
However, you must keep in mind that both Text.Combine function and “&” operator work in slightly different ways.
While Text.Combine function can combine multiple columns into a single text value, the “&” operator can only join two columns together.
I hope you understood these steps.
Thank you for reading!