Have you ever been working with a new Power BI Dataset and received a DataFormat.Error notification while working with your data model? It can be very frustrating and confusing, but we will walk through a few ways you can address the Power BI error and avoid it in the future.
It’s worth noting that this error can show up in both the Microsoft Excel and Microsoft Power BI. Both pieces of software use Power Query to import and prepare data for further processing.
In this example, we will use Power BI, but the steps to correct will be similar in Excel.
Data Format Errors are one of the most common Power Query Errors you’ll encounter.
Let’s take a look at how to fix them!
What Causes a Power Query DataFomat Error?
DateFormat errors occur when a failure occurs when converting data types. Data types are important in Power Query because the software needs to know how to interact with software. For Example, Power Query will add two numbers and produce the sum but when asked to add two pieces of text it will concatenate them together into a longer string.
The computer would be unable to calculate the sum of the number 15 + the text “Apples” and causes an error message.
Step 1.) Right Click the Table Name and Edit Query
The first step is to launch the Power BI Query Editor. You can do this either from the Report View or Data View selected on the left side of the screen, and right click the table name you are importing and click on Edit Query.
Step 2.) Identify the Applied Step Causing Error
The Applied Steps panel on the right side of the screen will show each data transformation step that happens when the data is being loaded into Power BI. Most often this will be the Changed Type Step.
The current column data type is noted at the top of the column with a small icon of numbers, a calendar, letters etc. You can also right click on the column and go to Change Type, it will show you the selected data type for that column.
If you’re using Power Query within Power BI, our following guide explains the differences between the available data types. They’re mostly the same as Excel.
Note: Another option for quickly identifying the assigned data type, and the step that it’s occurring is by using the Advanced Editor, available under the View tab. It will show you the Power Query Formula Language, also called “M”.
We like this method when working with large datasets with lots of columns. It’s also helpful if you know that a column has specifically changed.
Best Books to Learn Power BI
From Beginner to Advanced, Power BI books are great ways to learn new techniques and are great reference guides for mastering DAX.
Pop! Automation may receive affiliate commissions for purchases through these links at no additional cost to you.
Step 3.) Fix a DataFormat Error by Changing Data Type
To correct the error, you need to assign the correct data type to the column.
You can do this by right clicking on the column name, select Change Type and select the appropriate data type. In our Example, we will change the data type of the Sales Qty column to Whole Number.
Re-Assign all Data Types in Power Query
If you have a lot of columns and can’t identify the error, it can be easier to delete the entire Changed Type step and let Power Query Auto-Detect the types again. This also helps if columns were added or deleted in newer versions of incoming data.
First, delete the Changed Type Applied Step
Then navigate to Detect Data Type, under Transform section of the Power Query RIbbon
This will create a new applied step based on where your previous selection was. Make sure the new Changed Type applied step is in the same place as before to avoid additional errors.
Correcting DataFormat Error by using Replace Error
A second option is to Right Click on the Column Name, and click on Replace Error.
The screen will allow you to do a “Find and Replace All” for a specific value within the dataset. For this method to work, you would need all of the errors to be caused by a similar set of values.
If it is a handful of values, you can click on an individual error and view the value that is causing the error. The example below shows that the Sales Qty column has a numeric data type, but one cell has the word “Zero” in it causing the error. You could use Replace Error to change “Zero” to 0
Identify Errors Using Power BI Column Quality Preview
Microsoft Power BI has several capabilities to perform data analysis and to preview the data brought into your data model. You can turn on Column Quality under the View section of the Power BI Toolbar.
Column Quality will show you how many errors you have in each column. If you’re unable to change the data type in your column, you can right click on the Column Quality preview Remove or Replace errors from here.
You can click on Keep Errors to filter the view to a list of all errors within the column.
Using Chat GPT to Reconcile Errors and Write Formulas
Chat GPT is a Natural Language Processing chat bot developed by OpenAI. It’s been trained to understand and respond to requests in many different languages. It’s an incredibly valuable resource for asking questions about Power BI, whether you’re trying to better understand the meaning of an error or write a custom DAX Formula.
You can learn more about how to sign up and use this free tool here: How to Use Chat GPT with Power BI
Conclusion
While the DataFormat.Error Invalid Cell Value message can be frustrating and confusing at first, it’s quick and easy to update. First identify the cause of the error, or which Power Query step the step occurs. You can either delete the step and re-apply the auto detect data type steps, or you can right click on a column to change the data type.