Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter group of All Rows (Power Query)

After grouping and calculating all rows per group, how can you filter that column? 

I´ve seen some answers but it does not solve what I want to do. 

In the data below, I´d like to select the rows where [INFO] starts with "C". I need to do this in [AllData] (cannot filter before the group operation):

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXL29wWShgZKsToQAdeIECBphBBw9vdBCBiBVPiBVBgjBDBVwM2IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, INFO = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"INFO", type text}, {"VALUE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AllData", each _, type table [ID=number, INFO=text, VALUE=number]}})
in
    #"Grouped Rows"

 

 Cheers!

 

Need Help
  • FrankAT's avatar
    FrankAT
    6 years ago

    Hi Anonymous,

    see figure formular bar:

     

    Regards FrankAT

  • AnkitBI's avatar
    AnkitBI
    6 years ago

    Hi Anonymous 

     

    Not sure why you need after Group by however You can perform in same step as below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXL29wWShgZKsToQAdeIECBphBBw9vdBCBiBVPiBVBgjBDBVwM2IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, INFO = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"INFO", type text}, {"VALUE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AllRows", each Table.SelectRows(_,each Text.StartsWith([INFO],"C")), type table [ID=number, Info=text, Value=number]}})
in
    #"Grouped Rows"

     

    Thanks
    Ankit Jain
    Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

3 Replies

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    Hi Anonymous,

    see figure formular bar:

     

    Regards FrankAT

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    6 years ago

    Hi Anonymous 

     

    Not sure why you need after Group by however You can perform in same step as below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXL29wWShgZKsToQAdeIECBphBBw9vdBCBiBVPiBVBgjBDBVwM2IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, INFO = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"INFO", type text}, {"VALUE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AllRows", each Table.SelectRows(_,each Text.StartsWith([INFO],"C")), type table [ID=number, Info=text, Value=number]}})
in
    #"Grouped Rows"

     

    Thanks
    Ankit Jain
    Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi AnkitBI 

      Thanks for the answer.

      Imagine I want to group by [ID] and sum all [VALUES], but also want some data of the records where [INFO] is filtered. I need to filter after the group so I can do the sum operation of all [VALUES].