Forum Discussion
ellysoo
3 years agoRegular Visitor
Grouping with quantity
Hi, Can I ask how can I group the fruits with its quantity below and visualize in Power BI as Fruit Type and Total number of the specific fruits? Fruit Type (Basket 1) No for fruits (Basket ...
- 3 years ago
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhNLFLSUTIBYp/U3Pw8IG2mFKsTDecZGgAJoKrkDCBtDJbyL0rMS08Fco1Aco4FBTkgjqEJWBLONQISvkCF+RBDQHIwY0C2OSXmASHEulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fruit Type (Basket 1)" = _t, #"No for fruits (Basket 1)" = _t, #"Fruit Type (Basket 2)" = _t, #"No of fruits (Basket 2)" = _t]), ChangeTypeOfNumberCols = Table.TransformColumnTypes(Source,{{"No of fruits (Basket 2)", Int64.Type}, {"No for fruits (Basket 1)", Int64.Type}}), TableToColumns = Table.ToColumns(ChangeTypeOfNumberCols), JoinFruitAndNumberCols = {TableToColumns{0} & TableToColumns{2}, TableToColumns{1} & TableToColumns{3}}, TableFromColumns= Table.FromColumns(JoinFruitAndNumberCols, {"Fruit", "Total"}), GroupOnFruitSumTotals = Table.Group(TableFromColumns, {"Fruit"}, {{"Total", each List.Sum([Total]), Int64.Type}}), SortOnFruit = Table.Sort(GroupOnFruitSumTotals,{{"Fruit", Order.Ascending}}) in SortOnFruit
jgordon11
3 years agoResolver II
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhNLFLSUTIBYp/U3Pw8IG2mFKsTDecZGgAJoKrkDCBtDJbyL0rMS08Fco1Aco4FBTkgjqEJWBLONQISvkCF+RBDQHIwY0C2OSXmASHEulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fruit Type (Basket 1)" = _t, #"No for fruits (Basket 1)" = _t, #"Fruit Type (Basket 2)" = _t, #"No of fruits (Basket 2)" = _t]),
ChangeTypeOfNumberCols = Table.TransformColumnTypes(Source,{{"No of fruits (Basket 2)", Int64.Type}, {"No for fruits (Basket 1)", Int64.Type}}),
TableToColumns = Table.ToColumns(ChangeTypeOfNumberCols),
JoinFruitAndNumberCols = {TableToColumns{0} & TableToColumns{2}, TableToColumns{1} & TableToColumns{3}},
TableFromColumns= Table.FromColumns(JoinFruitAndNumberCols, {"Fruit", "Total"}),
GroupOnFruitSumTotals = Table.Group(TableFromColumns, {"Fruit"}, {{"Total", each List.Sum([Total]), Int64.Type}}),
SortOnFruit = Table.Sort(GroupOnFruitSumTotals,{{"Fruit", Order.Ascending}})
in
SortOnFruit