Remove Error not working

[Colleen]

I'm extracting data from excel where there are #REF errors in the spreadsheet. For some reason remove errors will not remove them. So far I have tried:

1. Remove errors function from remove rows
2. Delete row (this will not work as it cannot delete an error)
3. Replace errors
4. Removed alternate rows (targetting the single error row)
5. Refreshing data source

Even with multiple attempts on each of these steps, the result is either nothing (steps 1,3,4,5) or they won't work as they throw an error themselves (2).

Here is a screenshot to demonstrate my problem. The error is on the last row, and the #REF error is from the data source where a user has attempted to divide a cell by another #REF. I cannot change the data source.

Error cannot be removed

How do I overcome this problem?

[Anonymous]

hi @Colleen,

Follow this link,

https://community.powerbi.com/t5/Desktop/How-to-remove-error-rows-Remove-error-option-not-working/td...

regards,

Naveen

[JJJJJJJJ]

Please did you ever find a solution to this? I'm absolutley stumped in a similar situation, years later. The only thing I seem able to do is to fix the #REF! error in my source Excel file. This is a wierd scenario where it's essentially a step-level error, but it should be a cell-level error. Because of this, replace / remove errors is not working as that only picks up cell-level errors.

[Colleen]

Hi @Anonymous ,

I already saw that link and followed the solution which was to replace rows. As shown above this doesnt work.

Kind regards,

Colleen

[Leo99]

Hey Colleen,

    Follow all the steps in the link. Replace with value  "null". Then right click on the column header and select "Remove Empty"...worked for me 

Regards,

L