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
Groupedhi Greg_Deckler ,
Not getting any option to attach my excel.
can we try with this data :
mat_nbr ship_plant qty
123 11 10
123 22 20
123 33 30
123 44 40
789 66 70
789 77 10Hi 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
one question.. why change type is required?
