Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
hainguyenson
Frequent Visitor

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

Hi there,

I have a sample table

indexV1V2V3V4V5V6
1123123
21-13123
3123123
4-123123
5123123
6123-123
7123123
812312-1
9123123
101231-13

 

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

 

My result I want is:

indexV1V2V3V4V5V6
1123123
3123123
5123123
7123123
9123123

 

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

1 ACCEPTED SOLUTION
PowerBItes
New Member

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.

 

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

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

This solution is OK. But I have this message:

hainguyenson_0-1677129509950.png

 

PowerBItes
New Member

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.

 

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

serpiva64
Super User
Super User

Hi,

you can insert a column

serpiva64_0-1677099797689.png

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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors