Converting Text to Numbers in Power Query

Data types play a crucial role in Power Query. They determine how data columns are interpreted and how various formulas and operations are applied. Converting Text to Numbers in Power Query is one of the most common, especially when working with csv files or other flat file formats.

Text can be converted to a numeric data type using Power Query M formulas, or the Power Query editor

We’ll explain why you should care about data types, what they do, and various ways to convert them from using the no-code Power Query Interface, temporarily changing the type assessment using formulas, and using Power Query to automatically detect data types to avoid having to convert multiple or all columns of data before being able to transform and prep your data.

Let’s jump in!

The Importance of Data Types in Power Query

Data types tell Power Query how to assess columns in the context of applying different operations and formulas. When working in Power Query each column is assigned a data type, usually this will be text by default, but Power Query can also auto-detect data types and make its best guess by sampling your data.

Many formulas and operations only work with specific data types. You can only concatenate two text fields together, and you can only add two numeric fields together to produce the sum. This also goes for date data types and data specific formulas that require time intelligence, such as being able to calculate the last day of the month.

When there is a mis-match between the type of formula being applied to the data types of columns it’s being applied to the results will be an error. Power Query is not capable of applying numeric formulas to text, date formulas to numbers etc.

Because of these requirements, you’ll often have to convert text to numbers or convert between other data types.

Identifying Data Types in Power Query

Prior to converting between data types in Power Query, you’ll need to first identify the data type that you’re starting with. This can be done by viewing the top header rows of the Power Query Editor, where icons to the left of the column name notate the current data type assigned to the field.

Example of data types assigned to columns in Power Query

Each icon represents a different data type. A calendar represents a date, 123 is a whole number, 1.2 is a decimal, and ABC is a text column.

Another way to view the data type of a column, or to make edits on an applied step with multiple conversions happening at once, is to expand the Power Query Formula Bar above the headers of your data.

Example of the Power Query M code behind a data transformation step

You can also launch the Advanced Editor from the Home Tab of the Power Query Ribbon for more room). Here you can see the Power Query M code being generated that is assigning data types to each column.

Automatic Text to Number Conversions

The easiest way to convert text to numbers is if Power Query can detect the data type automatically. When you import data into Power Query, it will make an intelligent guess about each column’s data type. For example, when you import a column of dates, Power Query should automatically assign a date data type. The data type changes are tracked in the “Changed Type” Applied Steps on the right side of Power Query.

The automatic applied step to change data types when importing data into Power Query

Unfortunately, automatic detection doesn’t always work, especially if you have a column that is mixed with text and numbers. An incorrect automatic assessment of data types is also one of the biggest causes of errors or mis-calculations when using Power Query.

Automatically Detect Data Types in Power Query

To automatically detect data types in Power Query, go to the Transform section of the Power Query Ribbon, and select “Detect Data Type” in the “Any Column” section. A new “Changed Type” Applied Step will be created as Power Query makes its best guess for the appropriate data type for each column.

The auto detect data type button in the Power Query editor

Using the automatic data type detection feature can be a big time saver when working with many different columns. You can later, select the Changed Type Applied Step and make adjustments to it using the following method.

Converting Text to Numbers or Other Data Types

The fastest way to change text to numbers in Power Query is to right click on a column header, select “Change Type” and select the new data type of the column. When a new data type is selected it will create a Changed Type Applied Step and the icon at the top of the column will reflect the newly selected data type.

Changing the data type from text to number in Power Query

If you have a number of different columns to update to the same data type you can do so in a single step.

Converting Data Types of Multiple Columns or All Columns at One Time

To change the data type of multiple columns at one time in Power Query, first multi-select the columns you want to update. Either hold CTRL and left-click on columns or left-click on a column, hold SHIFT, then select another column to highlight a range of columns. Right-click on the selection and choose Change Type.

Changing multiple text columns to numeric data types in Power Query

Another options is to select all, by pressing CTRL +A to Select All which will highlight all of the columns in your data set. Then use the same method to right-click on a column header, Change Type and select the new data type.

Errors with Text and Numeric Data Types in Power Query

A very common Power Query error occurs when creating a new custom column and applying a formula where the data types don’t match. In the example below, we multiply the Sales Quantity Column (Text Data Type) with a Sales Price (Decimal Data Type). The resulting Extended Sales column is filled with Errors.

Example of a calculation error due to incorrect data types being assigned in Power Query

These errors occur because of a data type mismatch. Power Query is unable to calculate multiplication between a text and number column.

There are multiple ways to fix this problem.

The first, is to change the data type of Sales Quantity from Text to Numeric, but the change will be permanent in your data set. Depending on future transformations needed this may or may not be useful. Luckily, Power Query gives people the ability to temporarily change the assessment of data types when creating formulas.

Using the Power Query Number.From() Function for Data Type Conversion

To temporarily convert text to numbers in a Power Query M formula, use the Number.From formula to surround a text column. This changes the data type assessment for the specific formula being created without updating the primary data set.

The Syntax for our example from above is:

Number.From([Sales Quantity])*[Sales Price]

When using the Number.From function with multiple columns, you will need to apply the formula to each column name independently. If there are many different columns to update, it may make sense to multi-select the columns using the Power Query interface, create a formula, then change them back with multiple Applied Steps.

Correcting Conversion Errors in Power Query

In some cases, not all text values can be successfully converted to numbers. Typically, this is due to text characters being mixed in with number values. The errors won’t necessarily cause Power Query to error out completely, but can cause incorrect calculations. There are multiple options for handling errors during conversion.

  • Replace Values – Right-click on a column header and replace the text or non-numeric values with a zero or null value.
  • Filter Values – Use the Filter dropdown on a column header to filter out the errors, or non-numeric values to remove them from your data set when appropriate.
  • Correct Incoming Data – Go to the source data that Power Query is based on and update the incoming data.

In many scenarios our preference is to address these problems by fixing the incoming data as close to the source as possible. For example, data entry errors in a web form may be avoided by setting up data validation to allow only numeric characters entered into a number field.

It may not always be possible to fix data at the source, but it helps create a more repeatable and automated process in the future.

Summary

Converting text to numbers is a common data cleaning task in Power Query. Use automatic type detection when it works, or right-click on the column headers in Power Query to change the data type. When you only need to convert a data type temporarily, you can leverage the Number.From() function.

Errors during the conversion process generally occur when there is inconsistent data, these errors can be mitigated by updating source systems, replacing values, or filtering out the errors depending on the nature of your data set.

Scroll to Top