How to get rid of rows with empty values in column containing lists

[Anonymous]

Hi!,

I have dataset like this:

and I would like to remove rows like 234. How can I do it? There is no simple filtering option for that type of a column.

I cannot process this data any further because of the rows like this. After expansion of this column to new rows I get error rows which are irremovable by "Remove errors" option (another thing I cannot understand).

Regards,

Michal

[wdx223_Daniel · Accepted solution]

two methods to resolve this problem.

1, to transform this column before expanding it.

NewStep=Table.TransformColumns(PreviousStepName,{column name which you want to transform,each if _ is list then _ else {_}})

2, or to remove these blank rows before expanding

NewStep=Table.SelectRows(PreviousStepName,each [#"column name"] is list)

[Anonymous]

Thanks!

[lbendlin]

This is standard Power Query functionality. Click on the dropdown to the right of the column name and click "Remove Empty"

Or use the manual code approach.

let
    Source = #table({"A","B"},{{{"1","2"},"2"},{"","4"}}),
    #"Filtered Rows" = Table.SelectRows(Source, each [A] <> null and [A] <> "")
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Anonymous]

Notice that there is no dropdown in that type of a column.