PQ strange behavior when filtering errors

Power Query M Code Showcase
1

Hi All,

I have the following two queries. Frist one is working just fine, but the second one is giving an error and I can’t understand why. I use very simple table with just 4 rows. Thanks in advance!

Column1
123
456
789

as

let
Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
#“Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type number}}),
#“Removed Errors” = Table.RemoveRowsWithErrors(#“Changed Type”, {“Column1”}),
#“Filtered Rows” = Table.SelectRows(#“Removed Errors”, each ([Column1] <> null))
in
#“Filtered Rows”

////////////////////////////////////

let
Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
#“Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type number}}),
#“Filtered Rows” = Table.SelectRows(#“Changed Type”, each ([Column1] <> null)),
#“Removed Errors” = Table.RemoveRowsWithErrors(#“Filtered Rows”, {“Column1”})
in
#“Removed Errors”

1 Like
2

Hi @zhelyazkov1984ivan,

Welcome to the Forum.
How does the error message read?

Thanks.

3

Hi Melissa,

glad to be here. :slight_smile:
It is just breaking the query and doesn’t load the table. Here is the file.
Power Query Remove Errors not working why.xlsx (23.7 KB)

4

Explaining why, should probably be left to the MSFT Power Query team…

The error isn’t propagated somehow… Note that there’s also a potential risk of data loss when you select Source2 in the ChType step. Evaluating that, removes all rows below the error.

let
    Source1 = Table.FromColumns( {{123, 34, 12, "as", null}}, {"Column1"}),
    Source2 = Table.FromColumns( {{123, "as", 34, 12, null}}, {"Column1"}),
    Source3 = Table.FromColumns( {{"as", 123, 34, 12, null}}, {"Column1"}),
    Source4 = Table.FromColumns( {{123, 34, 12, null, "as"}}, {"Column1"}),
    ChType = Table.TransformColumnTypes(Source4,{{"Column1", type number}}),
    RemoveBlanks = Table.SelectRows(ChType, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    RemoveErrors = Table.RemoveRowsWithErrors(RemoveBlanks, {"Column1"})
in
    RemoveErrors

These all work (again, just change the table reference inside the ChType step to test each one of them). The basic process => Remove errors BEFORE removing Blanks.

let
    Source1 = Table.FromColumns( {{123, 34, 12, "as", null}}, {"Column1"}),
    Source2 = Table.FromColumns( {{123, "as", 34, 12, null}}, {"Column1"}),
    Source3 = Table.FromColumns( {{"as", 123, 34, 12, null}}, {"Column1"}),
    Source4 = Table.FromColumns( {{123, 34, 12, null, "as"}}, {"Column1"}),
    ChType = Table.TransformColumnTypes(Source4,{{"Column1", type number}}),
    RemoveErrors = Table.RemoveRowsWithErrors(ChType, {"Column1"}),
    RemoveBlanks = Table.SelectRows(RemoveErrors, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    RemoveBlanks

Or maybe a better approach would be a custom filter and leave the ChType step to the end.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilterRows = Table.SelectRows( Source, each [Column1] is number ),
    ChType = Table.TransformColumnTypes(FilterRows,{{"Column1", type number}})
in
    ChType

.
Interesting…

1 Like
5

Hello @zhelyazkov1984ivan

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you