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

Query Error - Rows loaded with 1 error

I am dumping data from a tabular model, and the data is loading successfully except for 1 error. How can I identify what this error is? It appears that whatever error the query is running into is causing my data to cut off at that point, causing me to miss/lose over 15% of my dataset. I've had this happen in a couple of my models and validated that it only happens in Power BI -- it doesn't happen if I dump into PowerPivot.

 

Capture.JPG

5 REPLIES 5
Super User
Super User

Re: Query Error - Rows loaded with 1 error

In the Query Editor there is a button for "remove errors" that may be able to assist you in solving this problem. It is on the ribbon in the Reduce Rows group.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




willnic Frequent Visitor
Frequent Visitor

Re: Query Error - Rows loaded with 1 error

Hi - thanks for the idea. I actually tried that this morning and still got the same message that there was 1 error, even though that step is built into my query.

pqian Senior Member
Senior Member

Re: Query Error - Rows loaded with 1 error

@willnic Try skipping 12million rows in the queries editor and see what the error is?

 

The error maybe data related (a transformation failed, a calculation overflowed, etc), or can be data size related (server gave up returning data, out of memory, driver failed, quota reach, etc)

 

If you can skip 12 million rows and load the rest of the data, that typically suggest that you have reached a limit of some sort on the server. Otherwise, you should be able to see what the error is and fix it.

willnic Frequent Visitor
Frequent Visitor

Re: Query Error - Rows loaded with 1 error

Thanks for the ideas. I think I figured out the issue, and I believe it was related to the server giving up on returning the data.

 

In case anyone runs into a similar issue... I was pulling in "Full Date" and then converting it from text to the date format within Query Editor. When I would refresh manually through the Power BI Desktop, I noticed it was going much slower than other models I've created (it would load in ~4,000 row batches whereas my other models will load in ~20,000 row batches). I deleted the step where I was changing the data format in Query Editor and refreshed, and it loaded much faster without the error. So I'm guessing the data type conversion was causing it to run much slower, which then in turn was hitting time limits on the server returning the data, causing it to cut off. 

 

I then just changed the data type under "modeling" in Power BI Desktop instead -- much better now.

 

 

So long story short, don't mess with the data types in Query Editor if you don't have to. Adjust data types once the data is already loaded from the Query.

san_jois Regular Visitor
Regular Visitor

Re: Query Error - Rows loaded with 1 error

Hi,

Thanks for helping out

I too encountered this error.. 'n' no. of queries loaded 9 errors

I appended similarly structured 05 tables.

Loading Individual tables had no problems..no errors. Once I append, this issue started. I homed on to one single table which was source of this issue by trying to append indivdually different tables.

To check if there are any errors in values, I selected all cells (~290 rows and ~20 columns) and did colour scale conditional formatting in excel and found no errors.

Then I deleted half of the rows in the issue table and found reduced errors.

By deleting the rows further, I identified one culprit row. Though all values seemed OK, I manually typed the dummy numbers. I found that in one of the cells, if I wrote whole no. the error was gone after refreshing. I checked all the other table columns and found them to be of format 'whole no.'. So the issue was updation of decimal no. in a format set as whole no.

Rearranged the data, format in all 05 source tables including Appended table and after refreshing no errors reported.