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
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 10
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
one question.. why change type is required?
Hi mhmmd_srf ,
The screenshot that you provided in your first response, the columns "mat_nbr" and "plant" has data type "text" and column "qty" type as "INT". Based on this i have changed my M code. It's optional , as per your requirement you can change the datatype.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi mhmmd_srf ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh