Power Query: Evasive Errors
2 June 2021
Welcome to our Power Query blog. This week, I look at a problem I came across when uploading a large dataset from Excel.
I have some data I have uploaded from Excel:
This all looks good; I’ll just filter my data…
Well that’s not so good. Apparently, I have an “invalid cell” somewhere. But I have no errors?
The first problem here is that I have more than 1,000 rows. The preview is only for 1,000 rows, so the bar that would warn me if there is an error, has not looked past this.
Here are my errors:
Since some of the cells in Excel contained errors, they have been imported. Therefore, I should be able to just delete my errors and continue, yes?
This all looks good; I’ll just filter my data…
The error is still there! I try replacing the error instead. I can get to this from the Transform tab, or by right-clicking on the column.
I try the filter again.
Still no luck. Let’s try coding. There is an M function I can use :
Table.RemoveRowsWithErrors(table as table, optional columns as nullable list) as table
This function returns a table with the rows removed from the input table that contain an error in at least one of the cells. If a columns list is specified, then only the cells in the specified columns are inspected for errors.
This sounds ideal; I’ll create a step to remove errors for the whole table, just in case.
The M code I have entered is:
= Table.RemoveRowsWithErrors(#"Replaced Errors")
I am hoping that has done the trick, so I try the filter again.
Rats. Should I give up and complain to Microsoft? Well, I can, but there is a way I might still get it to work, and it involves the step I just created.
I need to go back to the earliest point in my query where the columns exist. In my case, the data came from a folder, so I go back to the step where the rows from the workbooks were expanded. In this case, it will be fine to insert a step. I remove the other attempts at removing errors and try again.
The same code is then applied to a different step.
= Table.RemoveRowsWithErrors(#"Expanded Table Column1")
Back to the filter…
It works! This issue is a bug, but this seems to be a workaround. Incidentally, using the interface options to remove or replace errors at this point didn’t work – I tried. Therefore, the rule is, if encountering uploaded errors which will not respond to the normal error treatment, use the M function Table.RemoveRowsWithErrors() at the earliest point possible.
Come back next time for more ways to use Power Query!
