Forum Discussion

cris1196's avatar
cris1196
Helper I
3 years ago
Solved

In Power Query, get a value by group with condition

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.

Need Help
  • Ahmedy's avatar
    Ahmedy
    3 years ago

    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"

1 Reply

  • Ahmedy's avatar
    Ahmedy
    Frequent Visitor
    3 years ago

    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"