Convert Column into rows with concatenation

[mhmmd_srf]

Hi All,

I have data set like below:

And I want data set like below from abouve one:

I need this using Power Query. Can anyone please help me on this.

Thanks,
MS

[Greg_Deckler]

@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

[mhmmd_srf]

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

[v-dineshya · Accepted solution]

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]

one question.. why change type is required?

[v-dineshya]

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

[v-dineshya]

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

[mhmmd_srf]

hi @Greg_Deckler ,

Not getting any option to attach my excel.