Forum Discussion
How to remove error rows ? Remove error option not working
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.
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.