Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
1 year ago
Solved

How can I group like rows ?

I have a table with the following:   NAME      KEY Added      DATA-123 Removed  DATA-123 Committed DATA-456 Added       DATA-789   What I need is to know if the KEY has a Removed, to filter t...
Need Help
  • EaglesTony's avatar
    EaglesTony
    1 year ago

    I decided on making a clone of the table and just having "removed" in there and merge it back and filter out those records with Key as null, it appears to work and needed to have this due to time sensitivity, but thanks all for your assitance and ideas.

ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

And another approach, which seems to execute quite rapidly:

 

Original

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJSU1R0lFycQxx1DU0MlaK1YlWCkrNzS/DFHbOz83NLClBSJiYmoElUAwxt7DEFDQ2MsRmMlg4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, KEY = _t]),
    
    filter = Table.SelectRows(Source, (r)=>
        let 
            allKeys = Table.SelectRows(Source, each [KEY] = r[KEY])
        in 
            not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase))
in
    filter

 

 

Result

 

 

EaglesTony's avatar
EaglesTony
Post Prodigy
1 year ago

Can you explain from a manual part (i.e. selected Name column then group by) instead of the Advanced Editor View ?

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    The code does not use GroupBy (and executes considerably faster). If you wanted to do it from the UI,

     1. when your table is showing, select the down arrow in the NAME column, and de-select "Removed"

      

     2. In the formula bar you will see something similar to below. "Source" will be the same as your  previous step.

           

     

     3. Replace what you see after the first comma with:

     

    (r)=>
        let 
            allKeys = Table.SelectRows(Source, each [KEY] = r[KEY])
        in 
            not List.Contains(allKeys[NAME],"Removed",Comparer.OrdinalIgnoreCase))

     

    resulting in:

     

     

     

     

     

     

    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      1 year ago

      When I filtered out "Removed", I have this line:

       

      #"Filtered Rows2" = Table.SelectRows(#"Added Custom4", each ([NAME] = "Added"))

       

      However, I still see "DATA-123" as Added in the table, the point is if DATA-123(or any other record) has both "Added" and "Removed" to remove both rows.

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        1 year ago
        EaglesTony wrote:

        When I filtered out "Removed", I have this line:

         

        #"Filtered Rows2" = Table.SelectRows(#"Added Custom4", each ([NAME] = "Added"))

         

        However, I still see "DATA-123" as Added in the table, the point is if DATA-123(or any other record) has both "Added" and "Removed" to remove both rows.

        That is because you did not follow my instructions precisely.

        It seems you have filtered something before you even got to entering my line.

         

        It's really hard to tell where you went wrong without knowing what you have done.

         

        I suggest:

        1. Make a copy of your query as it exists.
        2. Navigate to the step where you see the table as you show in your question.
        3. Delete all of the steps below that particular step.
        4. Then add the Filtered rows step as I showed you and edit in the formula bar as I described.