Remove rows based on condition in different columns (without column names)

[hainguyenson]

Hi there,

I have a sample table

index	V1	V2	V3	V4	V5	V6
1	1	2	3	1	2	3
2	1	-1	3	1	2	3
3	1	2	3	1	2	3
4	-1	2	3	1	2	3
5	1	2	3	1	2	3
6	1	2	3	-1	2	3
7	1	2	3	1	2	3
8	1	2	3	1	2	-1
9	1	2	3	1	2	3
10	1	2	3	1	-1	3

I want to remove rows that have negative value in each column: V1, V2, V3, V4, V5, V6.

My result I want is:

index	V1	V2	V3	V4	V5	V6
1	1	2	3	1	2	3
3	1	2	3	1	2	3
5	1	2	3	1	2	3
7	1	2	3	1	2	3
9	1	2	3	1	2	3

Currently, I'm using this query, the result is OK but it is very long:

Filter1 = Table.SelectRows(#"Changed Type", each [#"V1"] >= 0)

Filter2 = Table.SelectRows(#"Filter1", each [#"V2"] >= 0)

Filter3 = Table.SelectRows(#"Filter2", each [#"V3"] >= 0)

Filter4 = Table.SelectRows(#"Filter3", each [#"V4"] >= 0)

Filter5 = Table.SelectRows(#"Filter4", each [#"V5"] >= 0)

Filter6 = Table.SelectRows(#"Filter5", each [#"V6"] >= 0)

I will have problem if I have many columns. My idea is I will iterate all column start with V, I'm following this topic https://community.powerbi.com/t5/Power-Query/Remove-rows-based-on-condition-in-different-columns/m-p...  and trying another query:

= let     
    #"SelectColumns" = Table.SelectColumns(Source,List.Select(
        Table.ColumnNames(#"Changed Type"),
        each Text.Contains(_,"V"))),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Table.SelectColumns(#"SelectColumns") >= 0 then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

I have a problem with step: #"Added Custom".
How can I fix it? Please help.
Thanks in advanced.

File demo:
https://drive.google.com/file/d/1iTwykAmqw-g-6pdBEIAEQ9TRyuc5OYxk/view?usp=sharing

[wdx223_Daniel]

NewStep=Table.SelectRows(PreviousStepName,each List.Skip(Record.ToList(_),each _>=0)={})

[hainguyenson]

This solution is OK. But I have this message:

[PowerBItes · Accepted solution]

No frett. Here is an idea. In PQE you can add something like "V" to all the columns you want your logic to apply to. Change your column like this: 

To fix the issue in your query, you can modify the line where you add the custom column by using the following code instead:

 #"Added Custom" = Table.AddColumn(#"SelectColumns", "Custom", each List.AllTrue(List.Transform(Record.ToList(_), each _ >= 0)), type logical)

This code selects only the columns that have names starting with "V" by using the Table.SelectColumns function, and then adds a custom column that checks if all the values in each row of the selected columns are greater than or equal to 0 using the List.AllTrue and List.Transform functions. The type logical argument is added to explicitly set the data type of the custom column as logical.

After this modification, the rest of your query should work as expected to filter out rows with negative values in any of the columns.

Thanks for letting me help! If you found my response useful, please give me a thumbs up and consider visiting aimeetsanalytics.com for more insights and tips on data analytics.

[hainguyenson]

That's what I'm looking for. Thank you.

[serpiva64]

Hi,

you can insert a column

and then filter -1

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!