Forum Discussion
cris1196
3 years agoHelper I
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...
- 3 years ago
see attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhgeuBRTrN49taB-R?e=RW7ym9let 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"
Ahmedy
3 years agoFrequent Visitor
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"