If you’ve ever been confused by the Power Query message, DataFormat.Error: We couldn’t convert to Number don’t worry. It’s one of the most common issues that arise when working with Power Query and it’s quick to fix. We’ll explain what causes the error, how to fix it, and how you can avoid seeing it in the future.
Even though Power Query is available as a subset of features in Excel, it uses completely different formulas and requires a completely different way of working with data.
Rather than being able to update specific cells and reference formulas, Power Query requires users to work with data at a table and column level, which is more similar to working with databases than traditional spreadsheets.
Let’s dive in!
Table of Contents
What Causes an Excel Power Query DataFormat.Error?
The DataFormat.Error occurs in Excel Power Query when attempting to convert the data type of a column into one that does not fit with the data within the column. For example, converting text into a number or into a date. Because Power Query is unable to make the conversion the step results in an error.
The following DataFormat.Error is an example of an error that occurred when attempting to convert a column of State abbreviation Text into a Numeric data type.
Understanding how to work with data types in Power Query is one of the biggest differences of working in Power Query vs working in regular Excel worksheets.
Power Query Data Types Explained
Power Query utilizes the concept of data types to determine which type of operations can be performed on a column. For example, two columns of text are concatenated when added together while two columns of numbers produce the sum of the two columns. The data type of a column determines which functions are possible.
The current data type of a column can be found in Excel Power Query by looking at the icon above each column name.
Columns with letters are text columns, numbers are numeric, and dates will be notated with a calendar icon like the screenshot below.
Power Query will return a DataFormat.Error if the column data type being assigned doesn’t make sense for the data that exists in the column.
If we tried to change the City column in the example above into a date, it would return an error because it’s not possible.
How to Fix DataFormat.Error in Excel Power Query
To fix a DataFormat.Error in Power Query you need to make sure that assigned data types for each column are appropriate for the data in the column. Errors are typically caused by a Changed Type Applied Step. Select the step causing the error, and change the data type of the column that says “Error”.
To identify which Applied Step causes the problem you can click through the Applied Step bar on the right side of Power Query until the error message occurs. You can either delete the entire step and re-assign all data types, or you can look at the step prior to it to see what information the column contained.
To Delete an Applied Step, click the X on the left side of the step to delete it.
To change the data type of an existing column, right click on the column header and choose a new data type. Power Query will prompt you asking if you want to add it to the current step.
Assigning Data Types to Columns in Power Query
To change the data type of a column in Power Query, right click on the column header, navigate to Change Type and select the new data type for the column. Options include several types of number formats, date, time, and text formats.
Tip: To update multiple columns at once, you can hold down SHIFT and left click to select a range of columns or hold down CTRL + Left Click to multi-select columns. Once they’re selected you can right click and apply changes to them all at once.
As an alternative option, you can change the Data Type by selecting columns and using the Data Type drop down menu on the Home section of the Power Query Ribbon.
After you’ve assigned appropriate data types to each column the data format error will no longer appear.
The DataFormat.Errors message in Excel occurs when Power Query is unable to transform a column from one data type to another due to incompatible data being present in the column. For example, trying to convert a product description to a number.
When the error occurs, the column will show the word “error”. To fix the error, you can either delete the Change Type applied step on the right side of the Power Query Editor and re-assign data types or you can right click on the column and change the type to a compatible one.