Filter group of All Rows (Power Query)

[Anonymous]

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!

[FrankAT · Accepted solution]

Hi @Anonymous,

see figure formular bar:

Regards FrankAT

[AnkitBI · Accepted solution]

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]

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].

[FrankAT · Accepted solution]

Hi @Anonymous,

see figure formular bar:

Regards FrankAT