Forum Discussion
Convert Column into rows with concatenation
Hi mhmmd_srf ,
Thank you for reaching out to the Microsoft Community Forum.
Please follow below steps.
1. Imported sample data, that you have provided. Please refer snap.
2. In Power Query editor , New sources ---> Blank Query and click on "Advanced editor" delete the complete code and paste below M code.
let
Source = Table.FromRows({
{"123", "11", 10},
{"123", "22", 20},
{"123", "33", 30},
{"123", "44", 40},
{"789", "66", 70},
{"789", "77", 10}
}, {"mat_nbr", "ship_plant", "qty"}),
ChangedType = Table.TransformColumnTypes(Source, {
{"mat_nbr", type text},
{"ship_plant", type text},
{"qty", Int64.Type}
}),
Grouped = Table.Group(ChangedType, {"mat_nbr"}, {
{"ship_plant", each Text.Combine([ship_plant], ","), type text},
{"qty", each List.Sum([qty]), Int64.Type}
})
in
Grouped
3. Please refer below output snap and attched PBIX file.I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
mhmmd_srf If you post your data as text I can be more specific to your data but you can use this basic technique:
let
Source = Table.FromRows({
{1, "A"},
{1, "B"},
{2, "A"},
{2, "B"},
{3, "C"},
{4, "D"},
{5, "E"},
{6, "X"},
{6, "Y"},
{7, "X"},
{7, "Y"}
}, {"Value", "Value.1"}),
Grouped = Table.Group(Source, {"Value"}, {
{"Value.1 Combined", each Text.Combine(List.Transform([Value.1], Text.From), ","), type text}
})
in
Grouped
hi Greg_Deckler ,
Not getting any option to attach my excel.