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:
- 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,
- Number value = New calculated column
- CONVERT = Function Name
- financials = Table Name
- 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:
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.
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,
- Number value = New calculated column
- CONVERT = Function Name
- financials = Table Name
- 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:
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:
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 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:
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.
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.
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:
select the transform data option under the Home tab as highlighted below, and it will automatically redirects to the Power Query editor.
In the Power query editor, select and Right click on the Gross Sales column -> Change Type -> Whole Number option as illustrated below:
- 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.
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.
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:
- 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)
In the below screenshot, we can see that the new column displays the error value as highlighted.
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])
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.
- 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.
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.
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:
- How to Convert Number to Text in Power BI
- Concatenate Text and Number in Power BI
- Power BI DAX Min Date from Text
- How to convert decimal to whole number in Power Automate
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com