How to Convert Text to Number in Power BI

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

In addition, I will show you how to convert Text to a Number using the Power Query editor in Power BI. Moreover, We will also cover the topics below:

  • Power BI Convert Text to Number Error
  • Power BI cannot Convert Text to Number
  • Power Query Convert Text to Number
  • Power Query Convert Text to Number error

Power BI Convert Text to Number

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

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

We will use the financials data table in this example, to convert the Units Sold column which is of text data type to the number 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 Units Sold column data type by selecting the Units Sold column header, Column tools – > data type as highlighted below:
Power BI Convert Text to Number
Power BI Convert Text to Number
  • 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.
Number value = CONVERT(financials[Units Sold],INTEGER)

Where,

  1. Number value = New calculated column
  2. CONVERT = Function Name
  3. financials = Table Name
  4. Units Sold= Column Name
  • In the below screenshot, we can see that the new column displays the Whole Number data type value from the text data type as highlighted below:
Convert Text to Number in Power BI
Convert Text to Number in Power BI

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

Power BI Convert Text to Number Error

Let us see how we can fix the error and convert the string data type to the integer data type value in Power BI,

In this example, we will use the same units sold column value which is of string data type, now using the DAX formula we will convert it to the numeric value in Power BI.

Load data to the power bi desktop, create a new column in the data view, and apply the below formula:

Number value = VALUE("financials[Units Sold]")
  • When the above-mentioned formula converts the typed string, “Units Sold” column values, into the numeric value.
  • But in the below screenshot, you can see the error occurs because we count not use the value function to covert column values, it is only possible to convert single text values.
Power BI Convert Text to Number Error
Power BI Convert Text to Number Error

To achieve this, replace the below-mentioned DAX formula in the formula bar and select the check icon:

Number value = CONVERT(financials[Units Sold],INTEGER)

Where,

  1. Number value = New calculated column
  2. CONVERT = Function Name
  3. financials = Table Name
  4. Units Sold= Column Name
  • In the below screenshot, we can see that the new column displays the Whole Number data type from the String data type as highlighted below:
Power BI Convert Text to Number data type
Power BI Convert Text to Number data type

This is how to fix the error and convert the string data type to the integer data type value in Power BI.

Power BI cannot Convert Text to Number

  • No, it is completely possible to convert the text data type to the Number data type in Power BI.
  • There are two ways that we can achieve it, using the Power bi DAX formula and using the Power Query editor. You can simply follow this Power BI article to convert the String data type to the Integer data type with examples in Power BI.

Power Query Convert Text to Number

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

Yes, there are two different approaches that we can convert the text type to number 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 Manufacturing Price column which consists of the text data type, we will convert it to the number data type.

Follow the below steps to achieve it:

Load the data source into the Power Bi desktop, and to check the manufacturing Price column data type, select the manufacturing Price column header -> Column tools – > data type as displayed below:

Power Query Convert Text to Number
Power Query Convert Text to Number

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

Power BI Convert Text to Number direct query
Power BI Convert Text to Number direct query

In the Power query editor, select the manufacturing Price column, and under the Home tab, expand the Data type option and choose the data type as whole number data type as shown below:

Power Query Convert Text to Number example
Power Query Convert Text to Number Example

Once the data type has been selected as a Whole number type, it will be automatically converted to the number data type from the text data type for the selected manufacturing Price column.

= Table.TransformColumnTypes(#"Filtered Rows",{{"Manufacturing Price", Int64.Type}})
  • In the below screenshot, you can see that the manufacturing Price column has been converted from the text data type to the whole number 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.
Power Bi converts text to numeric
Power Bi converts text to numeric

To check the manufacturing Price column data type, select the manufacturing Price column header -> Column tools – > data type as displayed below, here we can see the changes have been reflected, and for the manufacturing Price column data type has been converted to a whole number data type.

Example of Power Query Convert Text to Number
Example of Power Query Convert Text to Number

This is one approach to convert the text to a number 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 text data type to the number 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:

Power BI Convert Text to Number in measure
Power BI Convert Text to Number in measure

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

Power BI Convert Text to Number direct query
Power BI Convert Text to Number direct query

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

Power BI Convert Text to Number DAX
Power BI Convert Text to Number DAX
  • Once the data type has been changed to the whole number data type, it will be automatically converted from the text data type to the number data type for the selected Gross sale column.
= Table.TransformColumnTypes(#"Changed Type",{{"Gross Sales", Int64.Type}})
  • In the below screenshot, you can see that the Gross sales column has been converted from the text data type to the number 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.
convert the text to number in a column in the power query example
convert the text to a number in a column in the power query example

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 whole number data type.

Power Query editor convert text to number data type
Power Query editor converts text to number data type

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

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

Also, read Power BI Multi-row Card

Power Query Convert Text to Number error

Here we will see how we can fix the text-to-number error using the Power Query editor in Power BI.

In this example, we will use the COGS column value which is of string data type, we will convert it to the number value in Power BI.

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

Power Query Convert Text to Number error
Power Query Convert Text to Number error
  • Under the Home tab select the transform data option as highlighted below, it will automatically redirect to the Power Query editor.
  • Select Add column -> Custom column option, enter the below-highlighted formula, and click the ok button.
=Number.FromText([COGS],type number)
Power Query Convert Text to Number error example
Power Query Convert Text to Number error example

In the below screenshot, we can see that the new column displays the error value as highlighted.

Example of Power Query Convert Text to Number error
Example of Power Query Convert Text to Number error

To achieve this, replace the below-mentioned formula in the formula bar and select the ok button, it directs changes to any data type, we don’t need to mention the data type specifically.

Number.FromText([COGS])
convert text to number data type in Power Query editor
convert text to number data type in Power Query editor

The Screenshot below displays the newly added column in any data type, now change it to the whole number type by selecting the column -> Home -> Data type -> Whole Number data type.

Convert Text to Number error in Power Query
Convert Text to Number error in Power Query
  • Once the data type has been changed to the whole number data type, it will be automatically converted from the text data type to the number data type for the selected COGS column.
= Table.TransformColumnTypes(#"Added Custom",{{"Text to Number", Int64.Type}})
  • In the below screenshot, you can see that the COGS column has been converted from the text data type to the number data type in the Power Query editor.
  • Select the Home ->  Close and Apply option, so that the changes will be reflected on the Power Bi desktop.
Convert Text to Number error in Power Query example
Convert Text to Number error in Power Query example

To check the COGS column data type, select the newly added column header -> Column tools – > data type as displayed below, here we can see the changes have been reflected and the COGS column data type has been converted to whole number data type from the text data type.

Power BI Convert Text to Number direct query example
Power BI Convert Text to Number direct query example

This is how to fix the error and convert the string data type to the number data type value in Power BI.

In this Power Bi tutorial, We have discussed how to convert Text to Number in Power BI. Also, We saw how to work with Power BI to convert text to numbers using Dax.

In addition, I also showed you how to convert Text to a Number using the Power Query editor in Power BI. Moreover, We also covered the topics below:

  • Power BI Convert Text to Number Error
  • Power BI cannot Convert Text to Number
  • Power Query Convert Text to Number
  • Power Query Convert Text to Number error

You may like the following Power BI tutorials:

>