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.

edhans's avatar
edhans
Community Champion
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.