Forum Discussion
Solved
[Power Query] Get max value of rows with grouping.
I am trying to group data set to reflect the Max of the Total column. I have this data set Date ProductID Price Orders Total 1-Jan a12 11 1 11 1-Jan a13 12 3 36 1-Jan a...
Hi Beck ,
You can use the group by feature:
Here's a sample code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtT1SsxT0lFKNDQCkoaGIALCiNVBljUGCYKUgBjGZrhkwdgELGuEabIJCOOUBes1wiVrCsKmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ProductID = _t, Price = _t, Orders = _t, Total = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ProductID", type text}, {"Price", Int64.Type}, {"Orders", Int64.Type}, {"Total", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "ProductID", "Price"}, {{"Orders", each List.Max([Orders]), type nullable number}, {"Total", each List.Max([Total]), type nullable number}}) in #"Grouped Rows"
Hi Beck ,
You can use the group by feature:
Here's a sample code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtT1SsxT0lFKNDQCkoaGIALCiNVBljUGCYKUgBjGZrhkwdgELGuEabIJCOOUBes1wiVrCsKmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ProductID = _t, Price = _t, Orders = _t, Total = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ProductID", type text}, {"Price", Int64.Type}, {"Orders", Int64.Type}, {"Total", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "ProductID", "Price"}, {{"Orders", each List.Max([Orders]), type nullable number}, {"Total", each List.Max([Total]), type nullable number}})
in
#"Grouped Rows"