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...
  • Ahmedy's avatar
    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"