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 1)Fruit Type (Basket 2)No of fruits (Basket 2)
Pear4Lemon6
Lemon10Peach3
Orange20Apple 14
Apple12Mango10
Peach4Banana6

 

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

1 Reply

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