How to Convert Number to Text in Power BI

While working on a Power BI report for a Sales team, one of my colleagues asked me how to convert numbers to text in Power BI.

This was because, in the source data, one column contained both numbers and text values. When this column was used in a chart or visual, Power BI started showing errors, and the report did not work as expected.

In this tutorial, I’ll tell you the different ways to convert numbers to text in Power BI, including:

• Convert number to text using Power BI DAX
• Convert number to text using a calculated column in Power BI
• Convert number to text using Power Query (2 different methods)

Convert Number to Text in Power BI

In this section, we will learn how to convert a number data type to a text data type in Power BI

In this example, we will use the Financials table. The Sales column is currently a decimal number data type, and we will convert it into a text data type in Power BI.

To do this, follow the steps below:

1. Open the Power BI Desktop and load the Financials data table using the Get Data option. Then click on table view.

2. Next, select the Sales column. Now go to Column tools → Data type to check the current data type of the Sales column, as shown below.

3. Now, go to Column tools and select the New column option, as shown below.

4. Then add the formula below in the formula bar.

Text value = FORMAT(financials[ Sales], "#")

Where:

• Text value = New calculated column
• FORMAT = Function Name
• financials = Table Name
• Sales = Column Name

5. Now, you can see that the new column is created, and its data type is set to Text, as highlighted below.

This is how you can convert a number data type to a text data type in Power BI.

Convert Number to Text Datatype using Power BI DAX

Now, let us see how to convert a number data type to a text data type using Power BI DAX functions.

In this example, we will convert the Manufacturing Price column from a whole number data type to a text data type in the data table.

1. Open Power BI Desktop, load the Financials data, and then go to the Table view.
2. Select the Manufacturing Price column header -> Column tools -> data type. You can see the datatype.

3. Now, select the New column option under Column tools. Enter the formula shown below in the formula bar, and then click the check icon.

Text type value = FORMAT(financials[Manufacturing Price], "#")

Where:

• Text type value = New calculated column
• FORMAT = Function Name
• financials = Table Name
• Manufacturing Price = Column Name

4. The screenshot below shows the new column, with its data type highlighted as Text.

This is an example of converting a whole number data type to a text data type using a Power BI measure.

Convert Number to Text Datatype using Calculated Column in Power BI

Here, we will convert a number to a text data type and show the result in a calculated column in Power BI.

In this example, we will convert the Discounts column from a fixed decimal number data type to a text data type in the Financials table.

Follow the steps below:

1. In Power BI Desktop, go to the Table view. Select the Discounts column header, then go to Column tools -> Data type, as shown below.

2. Now, select the New column option under Column tools. Enter the formula shown below in the formula bar, and then click the check icon.

Text calculated column = FORMAT(financials[Discounts], "#")

Where:

• Text calculated column = New calculated column
• FORMAT = Function Name
• financials = Table Name
• Discounts = Column Name

In the screenshot below, we can see that the new column displays the Text data type as highlighted below:

This is how you can convert a number to a text data type and display the result in a calculated column in Power BI.

Convert Number to Text Datatype using Power BI Power Query

Now, let us see how to convert a number data type to a text data type using the Power Query Editor in Power BI.

Here, I will show you two different ways to convert a number data type to a text data type using the Power Query Editor.

Method 1:

In this example, we use the Financials table data and convert the Units Sold column from a decimal number data type to a text data type.

To do this, follow the steps below:

1. Load the Financials data table into Power BI Desktop.

2. Under the Home tab, select the Transform Data option, as shown below.

3. In the Power query editor, select the units sold column and under the Home tab, expand the Data type option and choose the data type as text, as shown below:

4. In the screenshot below, you can see that the Units Sold column has been converted from a number data type to a text data type in the Power Query Editor.

This is one way to convert a number to a text data type using the Power Query Editor in Power BI.

Method 2:

In this example, we will use the Financials table and convert the Gross Sales column from a decimal number data type to a text data type.

2. Open Power BI Desktop and load the data. To check the data type of the Gross Sales column, select the column header, then go to Column tools → Data type, as shown below.

2. Select the Transform Data option under the Home tab, as shown below.

3. In the Power Query Editor, select the Gross Sales column. Right-click on it, then choose Change Type -> Text, as shown below.

Now, load the data. Go to the Table view and select the Gross Sales column header. Then go to Column tools → Data type, as shown below.

Here, you can see that the Gross Sales column data type has been changed to Text.

These are the two different ways to convert a number data type to a text data type using the Power Query Editor in Power BI.

In this tutorial, I covered different ways to convert numbers to text in Power BI. I explained how columns that contain both numeric and text values can cause errors in charts and visuals.

Also, I covered how to convert numbers to text using Power BI DAX, how to display the result using a calculated column, and how to convert numbers to text using Power Query with two different methods.

You may like the following Power BI tutorials:

• Add an Empty Column in Power BI
• Sort Slicer by Another Column in Power BI
• Add Index Column Using Power Query Editor in Power BI
• The Best Way to Add a Hyperlink to a Text Column in Power BI