Forum Discussion
Advocate IIView Errors. OK, Where are they?
I have refreshed my data set. After it completes, I get a notification that there are errors. I click view errors. The Transform Data/Power Query Window opens, and no data loads.
I look at the refreshed table, and examine it using View|Column Profile. No columns have errors. Maybe because it is only loading 1000 rows. So, where are the errors?
Do I have to load all 695K rows?
9 Replies
Hi comish4lif2 ,
In general this happens when there are no errors in the query, but errors occur when the query results are loaded into the data model.
Examples:
- unique key values in Power Query (case sensitive), that are not unique when loading (case insensitive),
- numbers with fractions in a column with data type Int64.Type (Whole Number),
- etcetera.
Or you can have a try.
In power query-> "remove rows" section, click remove errors and check if error disappeared.
Maybe some calculations that used in the query resulted in "NaN" and "Infinity" in some cells which were actually the errors.
converted the columns to "text" from decimal # in the query editor and then replaced values (NaN, Infinity) with Blank. and then converted the columns back to decmal # and saved it.
Please choose a way which suits you.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
comish4lif2There's no calcutions.
There's no fractions.
The errors that I can see are a GroupID that is initially identified as a number (based on the sampling of the first 1000 rows by BI). If I change the datatype to text, Close and Apply, it still shows the errors.Seems like it's a bug.
Hi comish4lif2 ,
Please click the steps on the Power Query. Check which step causes the error.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Go to edit query. You should be able to see the rows contating the error separately in Queries section in the left hand side.
Adamp1916Helper I
When you find the row with the error, then what? Nothing really tells you what's wrong with it. Just that there's an error somewhere in that row.
In the query editor, if you scroll through the columns, there should be at least one column that says "Error" in some or all of the rows. Then click in the empty space next to the word to see a description of the error at the bottom. Most of the time the error is caused by a problem with the data type the column is set to - like text in a date column. Next you should go back to the original table that had the errors and fix them there. Look for the earliest step where that column was changed to that data type, and change it to a text column or replace the values with null or blanks. Then see if the rows in the errors query are now empty. If not, keep looking for data type conflicts.
https://www.youtube.com/watch?v=Tg0RLwD9ehw
This video above is super helpful.
https://learn.microsoft.com/en-us/power-query/dealing-with-errors
This is very helpful as well.
