Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lavdeep
Advocate I
Advocate I

How to remove error rows ? Remove error option not working

Hi,

 

How to remove error rows ? Remove error option not work refer below SS

 

Row number 19Row number 19

when i click remove error its not working

 

saaaaaaagg.png

 

Error Still showing in data table 

please help how to remove this error row

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi lavdeep,

This one stumped me for a while I was.  I would transform data to eliminate errors and it just didn't take.  Eventually this is how I solved it:

1) Make sure you are in the Power Query Editor
2) Go to the "Transform" tab
3) Select the column you want to modify
4) Look for "Replace Values", and more importantly, the little down arrow next to it.  It's in the sub-section titled "Any Column"
5) Click the down arrow next to "Replace Values" and you'll see another option, "Replace Errors"

If you click that it should produce a dialogue which allows you to replace any errors in that column (or columns, if you select multiple) with a value of your choosing.

View solution in original post

10 REPLIES 10
AnnOminous
Employee
Employee

Another possible resolution (and, I think, the best one if you're able to do it) is to identify where in your query the errors are being generated and fix that.

In my case, I was invoking a custom function that generated multiple table columns. I ended up going through each one of those tables and found the table that had the error row (every field in that row returned Error, and no Remove Errors or Replace Errors step would resolve it). I then went back to the query from which the custom function was created and populated the values for the problematic table. Sure enough, there were a substantial number of entries that had Error in one of the columns. When I used Replace Error in that query, it fixed the issue in the query that was calling the Invoke Function step.

It was somewhat time consuming, and it may not always be possible to do this, but if you can, it's definitely the best approach, because it's actually addressing a problem with the data, not with PBI.

jblackcitrin
Frequent Visitor

Just in case this helps someone else, I was able to fix this by changing the ApiVersion from 14 to 15 in the Source line

jblackcitrin_0-1686697962669.png

 

I_am_Vengance
New Member

I had a similar problem with an excel dataset that I was bringing into the Power Query editor. After countless tries, I went into the source data and applied an IFERROR formula to result in "null" and then reloaded the file - which worked perfectly.

Anonymous
Not applicable

Did anyone reach a solution for this problem?

I cannot get it, why the remove errors option doesn't work here? even replacing the errors doesn't work at all.

jaryszek
Post Patron
Post Patron

Hi,

the provided solution is not working for me. Any other suggestions?


Jacek

Anonymous
Not applicable

I appreciate this solution, however, unfortunately it is still not solving my problem. I have a row deep in the dataset (row 3239, far beyond the 1000 row preview) that gives an error across all columns. The error shows the following:

 

Expression.Error: We cannot convert the value "University of Arizon..." to type Table.
Details:
Value=University of Arizona
Type=[Type]

 

I have tried removing rows with errors, replacing rows with errors, and even deleting individual columns until theres nothing left. I believe this is truly a defective row and want to get rid of it entirely but nothing is working. Any other suggestions? Could I write a conditional to remove any rows that contain "University of Arizona"?

Anonymous
Not applicable

Hi lavdeep,

This one stumped me for a while I was.  I would transform data to eliminate errors and it just didn't take.  Eventually this is how I solved it:

1) Make sure you are in the Power Query Editor
2) Go to the "Transform" tab
3) Select the column you want to modify
4) Look for "Replace Values", and more importantly, the little down arrow next to it.  It's in the sub-section titled "Any Column"
5) Click the down arrow next to "Replace Values" and you'll see another option, "Replace Errors"

If you click that it should produce a dialogue which allows you to replace any errors in that column (or columns, if you select multiple) with a value of your choosing.

This solution didn't work for me UNTIL I changed my order of Steps.

I had a Filtered Rows step and if I tried 'Removing Errors' or 'Replacing Errors' afterwards, then it failed.

But when I started applying this step and then Filtering, it worked!

 

Would love to know why, but in the meantime I'll just enjoy it and stop banging my head against the table 😅

Thank you SOOOO much!

If the errors are in several columns, then you can select all the affected columns and then use then select "Remove Errors"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.