cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Helper II
Helper II

Re: Identify Error Row

where is your data source? 

 

 

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

Highlighted
Frequent Visitor

Re: Identify Error Row

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.

Highlighted
Helper II
Helper II

Re: Identify Error Row

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. 

Highlighted
Frequent Visitor

Re: Identify Error Row

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.

Highlighted
Helper II
Helper II

Re: Identify Error Row

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. 

Highlighted
Resolver I
Resolver I

Re: Identify Error Row

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

Highlighted
Frequent Visitor

Re: Identify Error Row

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.

Highlighted
Microsoft
Microsoft

Re: Identify Error Row

@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,

Highlighted
Frequent Visitor

Re: Identify Error Row

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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors