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
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