Grouping with quantity

[ellysoo]

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)
Pear	4	Lemon	6
Lemon	10	Peach	3
Orange	20	Apple	14
Apple	12	Mango	10
Peach	4	Banana	6

[jgordon11 · Accepted solution]

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