Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |