Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

remove rows based on condition on another column

hello  I have data similar to this and I want to remove ids that have not go through C step    id steps 1 a 2 a 2 b 1 b 1 c 3 a   so the expected result is 1 ...
Need Help
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    Hi Anonymous 

     

    Insert this formula as a custom column in Power Query:

    let 
    varID = [id]
in   
Table.MatchesAnyRows(
    Table.SelectRows(#"Changed Type", each [id] = varID), each [steps] = "c"
)

    It will return this table:

    Then simply filter to filter out the "FALSE" values, and you will be left with your table above, the ID's that are 1 and have gone through step C.

     

    Note: this will work as long as there is a step C. If there is also a D, E, and F, it will still keep that data.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

thank you Mariusz but if I have more than two columns can I show all the columns?

edhans's avatar
edhans
Community Champion
6 years ago

Anonymous - my additional column will not prevent you from seeing any other columns. I avoided the Group By method because I suspected your data was a bit more involved than what you shared with us, and a Group By can give unwanted results if you have multiple "Group By" columns that have different data for your IDs.

 

If my formula doesn't work in your situation, please advise where it breaks down and I'll see if I can tweak it.