I receive the following notification in Power BI Desktop when I am loading a dataset. It says:
"944,644 rows loaded. 1 error"
Is there any way to identify which row is causing the error? The data set loads fine in Power BI Desktop, but it causes issues in the web version when refreshing. I have tried clicking "Remove errors" in the query editor, but that doesn't help. I have also tried adding a custom column with the formula IsError(), but it doesn't find any rows with an error. I even tried scanning through the entire dataset to see if I could find any indication of an error, but no luck.
Here is a screenshot of the issue:
After the "Filltered Rows" step,
1- Add new column "Add Index" step will be created
2- Name it "Row Number" (again, "Renamed Columns" step will be created
3- Drag it to the left so an "Reorder Column" step will be created
Try Enabe Tracing under File -> Options -> Diagnostics. Then try to reload your Excel file. go back to the Options and click on the hyperlink called "Open Traces Folder". Once there, open the file that starts with PBIDesktop and look for any exceptions.
To me the message is clear, the operation succeeded with 1 error. If I have to take a guess, it has to do with data type mismatch.
Give it a try and let me know.
Thanks -- I turned on the tracer and found the file beginning with PBIDesktop, but it has so much information in it that I cannot decipher what any of it means? It has thousands of rows.
Ultimately the data successfully loads into Power BI, so I guess it isn't a huge issue. I was hoping there would be an easy way to identify the row causing the error, so I can remove it from my file.
Please try to skip the correct rows and load rest data to see if the error rows can be loaded correctly. Taking too long time on loading data which may cause server give up returning data. Please refer to a similar thread below:
I had seen that thread, and it also did not solve the issue. I still cannot identify which row is having an error.
The report still loads fine, so performance hasn't actually been affected. However, I just wish there was a way to identify which row was causing the error message.
I'm pulling from an excel file that is stored on OneDrive, so it is technically an online source but the link I use is directly to the file.
when you open the file, are there 944,6445 rows in the document? or are there more?
if there are more I think it's timing out before pulling all the rows.
It is pulling 100% of the rows. Also, I receive the error when it has pulled the 100,000th row (approx), not at the end. The dataset pulls in under 30 seconds. It doesn't appear to be having any timeout issues from what I can tell.
Hmmm. The only time I've seen it do the one row error at the bottom, and i can't get rid of it with remove errors, is when it wasn't pulling all the rows and timing out. I'm stumped.
Learn how to create your own user groups today!
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!