As any other programming language, Power Query handles errors in its own unique way and the goal of this blog post is to give you a few hints on how to audit the errors or warnings that Power Query might throw your way.
Types of Errors
Power Query primarily handles 2 types or categories of errors:
- Step level Errors – it’s main symptom is that you query simply will not load giving you a message similar to the one below
- Value Level Errors – query will load, but will have a warning saying that it loaded with errors and give you a hyperlink to see what errors it had when loading. For example, you could click on the “1 error” hyperlink shown in the image below and Power Query will create a new query with only the rows that have errors so you can audit those rows specifically.
Ways to Audit Errors
The best way to audit the errors is to go into the Power Query editor window (fka Query Editor window) and go through the steps and values to read the error messages.
For both categories or types of errors, it is extremely important to understand the Error Message which provides a path (exactly where it happened, usually the first sentence in the error message) and checking the Error Reason (exactly why it happened, usually in the details section of the error message).
An example of a Step level error with its Error message is shown in the next image:
In some cases Power Query even gives us this “Go To Error” button so we can go to the first step where we hit the error so we can fix the issue. If Power Query doesn’t give you that button, or it doesn’t get you to the first step where you error was raised then it is helpful to navigate through the steps to find out where the error first appeared.
For Value Level errors the hyperlink to the errors is an invaluable help. Clicking that hyperlink will make Power Query automatically create another query, usually with the Format “Errors in [Name of Original Query]”, with only the rows that had errors in your original query and it’ll also add an Index Column (called Row Number) to tell you exactly in what row the error happened. You can click in the whitespace next to the value errors to see the Error Message in the Cell Preview pane as shown below.
One thing to mention is that you could’ve manually gone through your original query, select the fields that you want to audit and select the option called “Keep Errors” so you can only see the errors found in those specific fields/columns:
The Most Common Errors in Power Query
From my experience over the years, there are only a handful of errors that you’ll encounter in Power Query. There are so few that I created the next list of the most common errors that might come across when working with Power Query.
Most Common Step Level Errors
- A Data Source function Error – usually caused by Power Query not having the right credentials or unable to connect to the data source (wrong file path or server name in most cases). Example: A query is being pointed to a filepath that no longer exists and, since Power Query can’t find it or connect to it, it displays a DataSource Error as shown below
- Missing Columns Errors – usually caused when a step is referencing a column that no longer exists. Example: The report was doing a fill down operation over a column that was named “Employee”, but suddenly the files had a change so that column would have the name “Full Name” instead. This discrepancy gives us the errors below
Most Common Value Level Errors
- Conversion Errors – converting a text that isn’t a date to a date data type can bring an error. When a value can not be converted to the desired data type, its output will be an error as shown below (Power Query can’t convert the text string ‘——‘ to a date)
- Operation Errors – when a operation or a function requires a specific data type for a value, but we pass a completely different data type, then its output will be an error value. Example: in the next image you’ll see that I try multiplying a column that has a text value “1” against a column that has a numeric value 1. Since the Column1 is set to text, that is not the number 1, but just a text “1”, so that operation yields an error and the Details tells us that the operator (*) can’t be applied to it. Similar to this situation, you can find others with functions that only accept certain data types and we try to pass a completely different data type that causes errors.
To learn more cool techniques and more advanced scenarios with the M language, go ahead and enroll to the FREE trial of the Power Query Academy where I talk more in detail about these type of techniques.
Thanks million. Keep Rows –> Keep Errors, works for me.
I clicked on the Errors hyperlink and it does start to generate a table but the table ends up empty. The initial query shows that I have 209 errors but the error search shows an empty table.
that’s pretty interesting! I highly recommend that you post your full scenario and sample file on the official Power Query forum: https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery
i’m trying to use Merge-joins between table column getting
Error:DataFormat.Error:We couldn’t convert into number
The table have different header column names,Different number of records just trying to do simple vlookup
Hey! I’d recommend that you post your full scenario to the official Power BI or Power Query forum:
I believe you need to clean the two linking columns and make sure they are both text before the Merge. You can change them to any format after the Merge.
Hi – I have a lot of power query refreshes that occur via VB scripts overnight, and save each file when complete.
The issue I have is that sometimes they fail to refresh, but still save the file.
These files are then user by end users who have no idea the data has not refreshed.
Is there a way to audit files to see if power queries have failed (for any reason) without opening each file? TIA
Hey! Sadly, I have no idea how to do VBA, but you can always create a new column that has a time stamp of when the query was last refreshed. Similar to a NOW() function in excel
Hi! Have you ever had an error that power query won’t recognize as an error.
For my source, I’m feeding in a folder path that doesn’t exist and trying to create error handling for when folders go missing.
Power query correctly shows an error for the Source, but when I edit the m-code to be “try Source”, HasError comes back as “FALSE”.
Have you ever encountered something like this?
When using excel or any other file combination (under one folder) is their any storage limitation on Power BI? Could you please help on this?
1) When using excel or any other file combination (under one folder) is their any storage limitation on Power BI?
2) When using excel or any other file combination (under one folder) is their any number of files limitation on Power BI?
Could you please help on this?
Your comment is awaiting moderation.
1. Power BI only stores the resultset of your queries and not the full data source itself.
Check the official documentation for limitations and specifications for Power BI datasets:
2. No limitations in that area.
Data is getting loaded when load to “Only Create Connection” but unable to load as “PivotTable Report”
I’d encourage you to let the Microsoft team know about this by posting it on one of their official forums with some repro steps so they can test it on their environments.