How to Convert Number to Text in Power BI [With Leading Zeros]

In this Power Bi tutorial, We will discuss how to convert Number to Text in Power BI. Also, We will see how to work with Power BI to convert numbers to text using dax.

I will also show you how to convert number to text with leading zeros in Power BI.

Moreover, We will also cover the topics below:

• Power query convert number to text
• Power BI convert number to text calculated column
• Power BI convert Number to Text with Leading Zeros

Power BI convert number to text

Here we will see how to convert the number data type to text data type in Power Bi,

Yes, it is possible to convert the number data type to text data type in Power BI.

We will use the financials data table in this example, to convert the sales column which is of decimal number data type to the text data type in Power Bi.

To achieve this, follow the below-mentioned steps:

• Load the financials data table into the Power Bi desktop using the get data option. Once the data has been loaded, check the sales column data type by selecting the sales column header, Column tools – > data type as highlighted below:

• Now select the new column option under the column tools as shown below:

• And use the below-mentioned formula in the formula bar and click on the check icon.

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

Where,

1. Text value = New calculated column
2. FORMAT = Function Name
3. financials = Table Name
4. Sales = Column Name

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

This is how to convert the number data type to text data type in Power Bi.

Power BI convert number to text DAX

Let us see how we can convert the number date to text data type using the Power BI DAX function in Power Bi.

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

• Log in to the Power Bi desktop and load financial data into it, To check the Manufacturing Price column data type.
• Select the Manufacturing Price column header -> Column tools – > data type as pictured below:

• Now select the new column option under the column tools and use the below-mentioned formula in the formula bar and click on the check icon.

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

Where,

1. Text type value = New calculated column
2. FORMAT = Function Name
3. financials = Table Name
4. Manufacturing Price = Column Name

• The screenshot below displays the new column displays the Text data type as highlighted below:

This is an example to convert whole number data type to text data type using the Power Bi measure in Power Bi.

Power BI convert number to text calculated column

Here we will convert the number to text data type and display the result in the calculated column in Power Bi.

In this example, we will convert a fixed decimal number data type to the text data type for the discounts column presented in the financials data table.

• Open the Power Bi desktop and load financial data into it, and check the discount column data type.
• Select the discounts column header -> Column tools – > data type as shown below:

Now select the new column option under the column tools and use the below-mentioned formula in the formula bar and click on the check icon.

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

Where,

1. Text calculated column = New calculated column
2. FORMAT = Function Name
3. financials = Table Name
4. Discounts = Column Name

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

This is how to convert the number to text data type and displays the result in the calculated column in Power Bi.

Power query convert number to text

Let us see how we can convert the number data type to text data type using the Power query editor in Power Bi.

Yes, there are two different approaches that we can convert the number type to text type using the power query editor. In the following, I have explained the two different ways to achieve it.

Approach 1:

In this example, we use the financials table data, and we convert the units sold column which consists of decimal number data type to the text data type.

Follow the below steps to achieve it:

Load the financial data table into the Power Bi desktop, and to check the units sold column data type, select the units sold column header -> Column tools – > data type as displayed below:

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 units sold column and under the Home tab, expand the Data type option and choose the data type as text data type as shown below:

• Once the data type has been selected as text, it will be automatically converted to the text data type from the number data type for the selected Units Sold column.

= Table.TransformColumnTypes(#"Removed Columns1",{{"Units Sold", type text}})

• In the below screenshot, you can see that the Units sold column has been converted from the number data type to the text data type in the Power Query editor.
• Click on the Close and Apply option, so that the changes will be reflected on the Power Bi desktop.

To check the units sold column data type, select the units sold column header -> Column tools – > data type as displayed below, here we can see the changes have been reflected and the units sold column data type has been converted to text data type.

This is one approach to convert the number to text using the Power Query editor in Power Bi.

Approach 2:

Now let us discuss the second approach, and we will use the financials table data, and we convert the Gross Sales column which consists of decimal data type to the text data type.

Open the Power Bi desktop and load data into it, and to check the Gross Sales column data type, select the Gross sales column header -> Column tools – > data type as displayed below:

select the transform data option under the Home tab as highlighted below, and it will automatically redirect to the Power Query editor.

• In the Power query editor, select and Right click on the Gross Sales column -> Change Type -> Text option as illustrated below:

• Once the data type has been changed to text data type, it will be automatically converted to the text data type from the number data type for the selected Gross sale column.

= Table.TransformColumnTypes(#"Removed Columns2",{{"Gross Sales", type text}})

• In the below screenshot, you can see that the Gross sales column has been converted from the number data type to the text data type in the Power Query editor.
• Click on the Close and Apply option, so that the changes will be reflected on the Power Bi desktop.

• To check the Gross Sales column data type, select the Gross Sales column header -> Column tools – > data type as displayed below, here we can see the changes have been reflected and the Gross Sales column data type has been converted to text data type.

This is another approach to converting the number to text using the Power Query editor in Power Bi.

These are the two different approaches that we can convert the number data type to text data type using the Power query editor in Power Bi.

In this Power Bi tutorial, We discussed how to convert Number to Text in Power BI. Also, we saw how to work with convert number to text using DAX in Power BI.

Check out, How to Concatenate Text and Number in Power BI

Convert number to text with leading zeros in Power BI

Let us see how to convert number to text with leading zeros in Power Bi.

In this example, we will use the below-displayed table data, you can download it from here and use it in the Power Bi.

• The leading zero is added only to the numbers less than 10. So that the outcome is always a two-character text.
• In this example, we will add the leading zero’s to the Month Number column presented in the Table data.

To achieve this follow the below-mentioned steps:

• Open the Power Bi desktop and load the Products Ordered data table by using the get data option. Once the data has been loaded, check the Month Number column data type by selecting the Month Number column header, Column tools – > data type as highlighted below:

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 include leading zeros. the D2 displays the two characters as an output, and click on the OK button.

= Number.ToText([Month Number],"D2")

In the below screenshot, you can see that the new custom column has displayed the values with leading zero with two-digit character output.

= Table.AddColumn(#"Renamed Columns", "Number to Text", each Number.ToText([Month Number],"D2"))

• Now change the Any data type value to text data type value by expanding the Data type option and choosing the data type as text data type as shown below:

= Table.TransformColumnTypes(#"Added Custom",{{"Number to Text", type text}})

• In the below screenshot, you can see that the custom column has been converted from any data type to the text data type in the Power Query editor.
• Click on the Close and Apply option, so that the changes will be reflected on the Power Bi desktop.

To check the custom column data type, select the number to text column header -> Column tools – > data type as displayed below, here we can see the changes have been reflected and the number to text data type has been converted to the text data type with leading zeros in Power Bi data view.

This is how to convert a number data type to a text data type with leading zeros in Power BI.

Moreover, We also covered the topics below:

• Power BI convert number to text calculated column
• Power query convert number to text
• Power BI convert Number to Text with Leading Zeros

You may also like the following Power BI tutorials:

• How to sort by multiple columns in Power BI
• Power BI sort table by Date
• Dataverse Power BI