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 

 

idsteps
1a
2a
2b
1b
1c
3a

 

so the expected result is

1a
1b
1c

 

I have tried this one but the problem is that removes all related steps too  

 
Table = CALCULATETABLE(table,FILTER (
FILTER (
ALLEXCEPT ( table,table[id]),
table[steps] = "c"
))
 
and if I can do it in power query direct without creating a new table that will be perfect
 
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.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Well, here is a DAX way, attached. Might be a little over complicated but I wrote it and it worked first time so that's always a little scary. I keep thinking there should be a more elegant way with EXCEPT or INTERSECT something.  ImkeF and edhans can potentially help with a Power Query solution. PBIX is attached.

    Table 2 = 
    VAR __Table = 
        SUMMARIZE(
            'Table',
            [id],
            "HasC","c" IN SELECTCOLUMNS('Table',"steps",[steps])
        )
    VAR __IDsWithC = FILTER(__Table,[HasC])
RETURN
    FILTER('Table',[id] IN SELECTCOLUMNS(__IDsWithC,"id",[id]))

     

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Anonymous 

       

      Sure, you can Group by id, later filter for any rows containing "c" and expand.
      Please see the script below or attached at the end.

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjJCYSWBWYYorGQwyxiiLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, steps = _t]),
    #"Grouped Rows" = Table.Group(Source, {"id"}, {{"rows", each _, type table [id=number, steps=text]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each List.Contains( [rows][steps], "c" ) ),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Filtered Rows", "rows", {"steps"}, {"steps"})
in
    #"Expanded Rows"

       

      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      LinkedIn



       

      • 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
    Icon for Community Champion rankCommunity 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.