cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwinkels
Frequent Visitor

Identify Error Row

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:

 

error.jpg

 

 

10 REPLIES 10
ayfalharbi
New Member

 

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

🙂 

felyoubi
Resolver I
Resolver I

Hi,

 

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.

 

-Fahd

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.

@mwinkels

 

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:

 

http://community.powerbi.com/t5/Desktop/Query-Error-Rows-loaded-with-1-error/td-p/23755

 

Regards,

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.

Beckham
Helper II
Helper II

where is your data source? 

 

 

are you pulling locally from a database or from an online source?

mwinkels
Frequent Visitor

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. 

mwinkels
Frequent Visitor

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. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!