How to Convert Text to Numbers in Power BI

Converting Texting to Numbers in Power BI can be accomplished as a data transformation step with the Power Query Editor or through DAX formulas. Incorrect data type assessment or inconsistent data can lead to data type errors that require an update to the incoming data or data transformation steps.

Converting text to numbers in Power BI can be accomplished with DAX formulas or through the Power Query Editor

Data Types play a key role in Power BI, determining how functions and subsequent data transformation steps are applied. Mathematic formulas can only be applied to numeric columns while text functions such as concatenate and parsing can only be done with text fields. We’ll explain multiple methods for converting text to numbers and explain how to troubleshoot errors when they appear.

Let’s jump in!

The Importance of Data Types in Power BI

Power BI supports 12 different data types which are broadly grouped into Numbers, Dates, and Text. Numbers and dates are further split out with different levels of precision. For example, dates could be date only, include date and time or include date time and time zone.

Every column that’s imported into Power BI is assigned one of these data types. The data types control how Power BI interprets the data inside of the column when formulas are applied. Numbers can be added to numbers, text can be concatenated but numbers and text can’t be combined. This goes for numbers and date, text and dates etc.

By default, Power BI will import data and make it’s best guess as to which data type is appropriate for a column. It does this by taking a sample of rows from each column and making its best guess or using meta data that’s embedded into the source file.

Power BI generally does a good job but doesn’t always get it right, requiring developers to convert from one data type to another.

Identifying Data Types in Power BI

To determine the data types assigned to columns in Power BI, review the icon to the left of each field name in the Data Panel or launch the Power Query Editor where there is an icon next to the header of each column that notes the currently assigned data type.

Example showing how to identify the data type assigned to each column in Power BI

Dates will appear as a calendar, the number icon varies depending on the sub-type, and text is noted as ABC in the Power Query Editor. Text columns are blank when viewing the data type in the Data Panel of the report builder view of Power BI.

Converting Text to Numbers with Power Query

To convert text to numbers in Power BI, launch the Power Query Editor then right click on a column with a text data type, select Change Type, and the desired numeric data type.

The example below shows the contextual menu that appears when you right-click on a column header in Power BI. Among the ability to change data types are many of most common data transformations applied in Power Query.

Converting text column to a numeric column using the Power Query editor contextual menu

Using the contextual menu is our preferred way to navigate Power Query because of how easy, fast and convenient it is. You can also adjust data types from the Transform section of the Power Query Ribbon. Select a column and change the Data Type using the dropdown menu under the Any Column section.

Converting data types using the Power Query Ribbon

After completing the data type conversion from text to numbers a new applied step will appear on the right side of Power Query called “Changed Type”. This step can be deleted or written over if you need to modify the updated data type.

Converting Multiple Columns of Text to Numbers with Power Query

To update the data types of multiple columns at one time in Power BI, launch the Power Query Editor. Then hold CTRL and left-click to select multiple columns. Hold SHIFT + left-click to select a range of columns or press CTRL + A to select all columns. Then right-click a column header, select Change Type, and the new data type.

The example below shows two text columns selected and being converted to a whole number.

Converting multiple or all columns in Power BI from text to number at the same time

Even though multiple columns are being converted from one data type to another, the results will be a single Changed Type applied step that captures all of the different data type conversions that are happening across columns.

Converting Text to Numbers with DAX Functions

To convert Text to Numbers with DAX, add a New Column in Power BI and use the formula: VALUE(Table[Column]) or use the CONVERT function for more control using the formula: CONVERT(Table[Column],Data Type). These formulas will dynamically convert text to numbers or other data types.

The examples below show what each formula looks like when applied to a data set. Our table is named “Example” with a column named “Values”

VALUE is great for quick conversions from text to numbers.

Example of a VALUE DAX formula in Power BI

CONVERT is better when you need more control over which data type a value is being converted to.

Example of a CONVERT DAX formula in Power BI

When using DAX to convert from text to numbers, it may not be immediately clear if there are errors. Power BI will ignore a number of conversion errors and calculate based on the values that are available, meaning that you could have incorrect calculations being presented if incoming data isn’t converted in full.

Troubleshooting Text to Number Errors in Power BI

The most common cause of errors when converting text to numbers in Power BI is inconsistent data. When a text value appears within a column of numbers, Power BI will fail to convert it and display an “Error” in place of the value. To correct the error, right-click on the column to replace the text value with a 0 or null or update the incoming data.

Example of data conversion errors when converting from text to numbers

When data type conversion errors occur in Power BI, our preferred method to correct it is by updating the source data coming into Power BI. The closer you get to the source data, such as enforcing only numeric characters in a system where people are entering number values means that you won’t have to constantly make adjustments to the incoming data.

We recognize that this is often easier said than done and some systems don’t support data validation during the data capture step.

If there are multiple values that need to be replaced, click on the “Error” text and it will show you a new query that displays all values that failed to convert from text to number.

Data Type Best Practices in Power BI

Even though you can convert data types within Power Query or DAX formulas, our preference is to perform conversions in Power Query. It gives us the most visibility to conversion errors and has the added benefit of performing the conversion during scheduled refreshes instead of dynamically converting data types when a user interacts with a report.

When errors occur, we recommend trying to update the data in the source system or spend a little bit of time trying to determine the root cause of the data entry error. At types it can also be caused by a delimiting issue where an extra comma in a dataset throws the number of aligned columns off.

Data conversion errors can typically be fixed by replacing values or filtering out errors but can lead to incorrect results if there is an underlying issue with the base data your report or dashboard is being created with.

Conclusion

Text to number data type conversions in Power BI can quickly be performed by right-clicking on a column header in Power Query and selecting Change Data Type. Data types are how Power BI determines how to interact with a column and how to apply various functions. A mis-match between a function type such as applying a date formula to a string field will result in a calculation error when creating a new column.

Not all errors will cause full calculations to fail, so it’s important to pay attention when only some of the columns fail a data type conversion which can lead to inaccurate results. If you see an error after a data type conversion, click on it to view the values that are not converting. Research the source of the error and either replace, filter or correct the incoming data.

Scroll to Top