In Power Query, get a value by group with condition

[cris1196]

Hi All!

I have the following table:

Date		Order		Store		Filter
2022-05-01		25		5		False
2022-05-01		25		5		True
2022-06-01		38		7		True
2022-06-01		38		7		True
2022-07-01		82		5		False
2022-07-01		82		5		False

So, I want to group the values ​​by "Order", where if an order has at least one True, it returns True.

That is, the table should look like this:

Date		Order		Store		Filter
2022-05-01		25		5		True
2022-06-01		38		7		True
2022-07-01		82		5		False

I know how to do it in Dax, but for certain reasons I need to do it in Power Query and I'm not getting it done. Any help is welcome. Thank you.

[Ahmedy · Accepted solution]

see attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhgeuBRTrN49taB-R?e=RW7ym9

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDVMzIwMlLSUTIyBRIg7JaYU5yqFKuDSz6kqBQhbQaTNrYAEuYkSpvDpC2MsFqOUz4WAA==", 
    BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Order = _t, Store = _t, Filter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Order", Int64.Type}, {"Store", Int64.Type}, {"Filter", type logical}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", 
            each if Table.RowCount(Table.Distinct(_))>1 
            then Table.SelectRows(_,(x)=> x[Filter]=true) 
            else Table.Distinct(_)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Order", "Store", "Filter"}, {"Order", "Store", "Filter"})
in
    #"Expanded Count"