How to Concatenate Text and Number in Power BI

In this Power Bi tutorial, we will learn how to concatenate text and number in Power BI with examples. In addition, we will also cover the below topics:

• How to concatenate text and Number in Power BI using dax
• Concatenate text and number in Power BI using power query
• How to concatenate text and percentage in Power Bi
• Power BI custom column combines concatenate text and Number

Power BI Concatenate function

The Power Bi Concatenate function joins two strings into one string. The strings can include text data type or numbers data type.

The syntax for the Power BI Concatenate function is

CONCATENATE(<text1>, <text2>)

In the following, we will see an example of how to concatenate text and numbers in Power BI.

Power BI Concatenate text and Number

Here we will how to concatenate text value and a number value using the Power Bi Concatenate in Power Bi.

In this example, we will use the Products Ordered table to concatenate text value and number value in Power Bi. Where the Product Name column is of text data type and the Sales column is of Number data type.

• Open the Power Bi desktop and load the data into the it using get data option, click on the new column, and use the below formula to concatenate.

Text and Number = CONCATENATE('Products Ordered'[Product Ordered], CONCATENATE(", ", 'Products Ordered'[Sales]))

Where,

1. Text and Number = New Column Name
2. Products Ordered = Table Name
3. Sales, Product Ordered = Column Names

In the below screenshot, the newly created column has displayed the concatenated values based on the applied condition:

This is how to concatenate text value and a number value using the Power Bi Concatenate function in Power Bi.

Read Calculate Percentage of Rows in Power BI

Power BI concatenate text and Number using DAX

Let us see how we can concatenate a text value and a number value using the Power Bi Dax in Power Bi.

• Open the Power Bi desktop and load the data into the it using get data option, click on the new column, and use the below formula to concatenate.

Text and Number = 'Products Ordered'[Product Ordered] & " - " & 'Products Ordered'[Sales]

Where,

1. Text and Number = New Column Name
2. Products Ordered = Table Name
3. Sales, Product Ordered = Column Names

In the below screenshot, the newly created column has displayed the concatenated values based on the applied condition:

This is how to concatenate a text value and a number value using the Power Bi Dax in Power Bi.

Power Query concatenate text and Number

Let us see how to concatenate text and numbers using the power query editor in Power BI.

In this example, we will concatenate text and number values using the power query editor in Power Bi.

• Log in to the Power Bi desktop and load the data into it using the get data option Under the Home tab select the transform data option as highlighted below, it will automatically redirect to the Power Query editor.

In the Power query editor, select the Add Column-> Add a Custom column as shown below:

In the Custom column window, use the below formula to concatenate text and number, and click on the OK button.

[Product Ordered]&"_"&Number.ToText([Sales])

• In the below screenshot, you can see that the new custom column has displayed the values concatenated text and number.
• Click on the Close and Apply option, so that the changes will be reflected on the Power Bi desktop.

= Table.AddColumn(#"Removed Columns", "Using Power Query", each [Product Ordered]&"_"&Number.ToText([Sales]))

In the below screenshot, you can see that the using power query column displays the expected output in the Power Bi data view.

This is how to concatenate text and numbers using the power query editor in Power BI.

Power BI concatenates text and percentage

Here we will see how to concatenate text and percentage in Power Bi.

Yes, it is possible to concatenate text and percentage in the custom column. In this example, we will concatenate the product ordered column with the sales percentage column.

Open the Power Bi desktop and load the data into the it using get data option, click on the new column, and use the below formula to concatenate.

Text and Percentage = CONCATENATE('Products Ordered'[Product Ordered]& " -",FORMAT('Products Ordered'[Sales Percentage],"Percent"))

Where,

1. Text and Percentage = New Column Name
2. Products Ordered = Table Name
3. Sales Percentage, Product Ordered = Column Names

In the below screenshot, the newly created column has displayed the concatenated values based on the applied condition and also displayed the percentage value:

This is how to concatenate text and percentages in Power Bi.

Power BI custom column combines concatenate text and Number

Let us see how we can add a custom column and combine or concatenate text and numbers in Power Bi

There are two ways that we can add a custom column and combine text and numbers, They are,

1. Using Direct Query
2. Using Power Query

Approach 1 – Using Direct Query

Let us see how to add a custom column to combine text and numbers using the Power Bi DAX direct query in Power Bi

• Open the Power Bi desktop and load the Products Ordered data table by using the get data option. Once the data has been loaded.
• Click on the new column under the modeling tab and use the below-mentioned formula and click on the check icon.

Combine Values = COMBINEVALUES(",", 'Products Ordered'[Product Ordered], 'Products Ordered'[Sales])

Where,

1. Combine Values = New Column Name
2. Products Ordered = Table Name
3. Sales, Product Ordered = Column Names

In the below screenshot, you can see that the newly created column displayed the combined values as text and number as expected.

This is how to add a custom column and combine text and numbers using the Power Bi DAX direct query in Power Bi.

Approach 2 – Using Power Query

Let us see how to add a custom column to combine or concatenate text and numbers using the Power Query editor in Power Bi.

• In the same way load the Products Ordered data table by using the get data option.
• Then Select the transform data option under the home tab, and it will automatically redirect to the Power Query editor.
• In the Power query editor, select the Add Column-> Add a Custom column. In the Custom column window, use the below-mentioned formula to combine or concatenate text and numbers and click on the OK button as shown below:

Text.Combine({[Product Ordered],Number.ToText([Sales ])}," = " )

• In the below screenshot, you can see that the new custom column has displayed the combined text and number values.
• Click on the Close and Apply option, so that the changes will be reflected on the Power Bi desktop.

In the below screenshot, you can see that the combined text and number column displayed the expected output in the Power Bi data view.

This is how to add a custom column to combine or concatenate text and numbers using the Power Query editor in Power Bi.

This is how to add a custom column and combine or concatenate text and numbers in Power Bi.

In this Power Bi tutorial, we have learned how to concatenate text and numbers in Power BI with examples. In addition, we also covered the below topics:

• Power BI concatenate text and Number dax
• Power query concatenate text and Number
• Power BI concatenates text and percentage
• Power BI custom column combines concatenate text and Number

You may also like the following power bi tutorials:

• Power BI Zip Code Starting With 0
• How to Remove Leading Zeros in Power BI
• Power BI Add Leading Zero to Month
• Power BI Distinct Count with filter