Forum Discussion

ellysoo's avatar
ellysoo
Regular Visitor
3 years ago
Solved

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