Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mhmmd_srf
Regular Visitor

Convert Column into rows with concatenation

Hi All,

I have data set like below:

mhmmd_srf_0-1752689211692.png

And I want data set like below from abouve one:

mhmmd_srf_1-1752689248333.png

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

Thanks,
MS

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

vdineshya_1-1752736041415.png

 

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.

 

vdineshya_0-1752735917497.png

 

 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh

View solution in original post

7 REPLIES 7
Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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.

vdineshya_1-1752736041415.png

 

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.

 

vdineshya_0-1752735917497.png

 

 

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

hi @Greg_Deckler ,

Not getting any option to attach my excel.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.