Forum Discussion

mhmmd_srf's avatar
mhmmd_srf
Regular Visitor
1 year ago
Solved

Convert Column into rows with concatenation

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

Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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's avatar
      mhmmd_srf
      Regular Visitor
      1 year ago

      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's avatar
        v-dineshya
        Community Support
        1 year ago

        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