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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
GraceJinM
Frequent Visitor

How to merge multiple rows into one row based on section name?

Hello, 

 

I need to merge multiple rows of my data into one row based on the section name. I'd like to do this in power query.

 

Here is an example of what my data currently looks like: 

 

What my current data looks likeWhat my current data looks like

 

Here is what I want my data to look like:

 

What I want my data to look likeWhat I want my data to look like

 

 

 

Thanks!

2 ACCEPTED SOLUTIONS

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tx = List.Transform(
        List.Skip(Table.ColumnNames(Source)), 
        (x) => {x, (tbl) => 
            [col = List.RemoveNulls(Table.Column(tbl, x)),
            func = if col{0}? is text 
                then Text.Combine(col, ",")
                else List.Sum(col)][func]}
    ),
    group = Table.Group(Source, "Section", tx)
in
    group

View solution in original post

Anonymous
Not applicable

Hi @GraceJinM 

You can try the following.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Section"}, {{"Data1", each try List.Sum([Column1]) otherwise Text.Combine([Column1],",")}, {"Data2", each try List.Sum([Column2]) otherwise Text.Combine([Column2],",")}, {"Data3", each try List.Sum([Column3]) otherwise Text.Combine([Column3],",")}, {"Data4", each try List.Sum([Column4]) otherwise Text.Combine([Column4],",")}})
in
    #"Grouped Rows"

Output

vxinruzhumsft_0-1725242144368.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

11 REPLIES 11
GraceJinM
Frequent Visitor

Hi Everyone! Thank you for the responses so far. 

 

If anyone has this specific issue, then the solutions below will work for you; however, I realized I misrepresented my data and what I want my end product to look like. When I took a closer look at my data, I realized that there were mutliple rows that had responses in the same column. For this situations, I needed the value to be summed if it was a number and split by comma when it was text. 

 

Here is what my data actually looks like: 

GraceJinM_0-1725031242078.png

 

Here is what I want it to look like: 

GraceJinM_1-1725031276579.png

 

 

 

 

Hi @GraceJinM, different approach:

 

Output v1

dufoq3_1-1725606203388.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    GroupedRows = Table.Group(Source, {"Section"}, {{"All", each
        [ a = Table.RemoveColumns(_, {"Section"}),
          b = List.TransformMany(Table.ToColumns(a), each {
                     [ b1 = List.Transform(_, (w)=> try Number.From(w) otherwise w),
                       b2 = try List.Sum(b1) otherwise Text.Combine(List.Select(List.Transform(b1, Text.From), (w)=> not List.Contains({"0".."9"}, w)), ",")
                     ][b2] },
                     (x,y)=> y ),
          c = Table.FromRows({ {[Section]{0}} & b }, Value.Type(_))
        ][c], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

 

Output v2

dufoq3_2-1725606294457.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    GroupedRows = Table.Group(Source, {"Section"}, {{"All", each
        [ a = Table.RemoveColumns(_, {"Section"}),
          b = List.TransformMany(Table.ToColumns(a), each {
                     [ b1 = List.Transform(_, (w)=> try Number.From(w) otherwise w),
                       b2 = try List.Sum(b1) otherwise Text.Combine(List.Transform(b1, Text.From), ",")
                     ][b2] },
                     (x,y)=> y ),
          c = Table.FromRows({ {[Section]{0}} & b }, Value.Type(_))
        ][c], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Anonymous
Not applicable

Hi @GraceJinM 

You can try the following.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Section"}, {{"Data1", each try List.Sum([Column1]) otherwise Text.Combine([Column1],",")}, {"Data2", each try List.Sum([Column2]) otherwise Text.Combine([Column2],",")}, {"Data3", each try List.Sum([Column3]) otherwise Text.Combine([Column3],",")}, {"Data4", each try List.Sum([Column4]) otherwise Text.Combine([Column4],",")}})
in
    #"Grouped Rows"

Output

vxinruzhumsft_0-1725242144368.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

What about when have both numbers and text, as in Column3?

Anonymous
Not applicable

Hi,

Thanks for the solutions Omid_Motamedise and AlienSx offered, and i want to offer some more infotmation for user to refer to.

hello @GraceJinM , you can create a blank query and input the following code.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMSgaSUBSrAxEEotQ0CF2SgSwKROkZMJVOEAEjJM0gIUMI3xhNGUxxbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}, {"Column2", type text}, {"Column4", type text}, {"Column3", type text}}, "en-US"),{"Column1", "Column2", "Column4", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Section"}, {{"Data", each Text.Combine([Merged],",")}}),
    #"Trimmed Text" = Table.TransformColumns(#"Grouped Rows",{{"Data", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Data", each Text.Combine(List.RemoveItems(Text.Split(_,","),{""}),",")}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Cleaned Text", "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4"})
in
    #"Split Column by Delimiter"

 

Output

vxinruzhumsft_1-1725002369177.png

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I realized I misrepresented my data and what I want my end product to look like. When I took a closer look at my data, I realized that there were mutliple rows that had responses in the same column. For this situations, I needed the value to be summed if it was a number and split by comma when it was text. 

 

Here is what my data actually looks like: 

GraceJinM_0-1725042644945.png

 

 

 

Here is what I want it to look like: 

GraceJinM_1-1725042644972.png

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tx = List.Transform(
        List.Skip(Table.ColumnNames(Source)), 
        (x) => {x, (tbl) => 
            [col = List.RemoveNulls(Table.Column(tbl, x)),
            func = if col{0}? is text 
                then Text.Combine(col, ",")
                else List.Sum(col)][func]}
    ),
    group = Table.Group(Source, "Section", tx)
in
    group
Omid_Motamedise
Super User
Super User

Follow this step
this is your data in power query

Omid_Motamedise_0-1724970389411.png

 

right click on column section and use unpivot other columns to reach the next result

 

Omid_Motamedise_1-1724970421810.png

 

then select Attribute column and go to transform tab and pick Pivot column comand.
like the below image pic value for value column.

Omid_Motamedise_2-1724970496611.png

hit ok to reach your result as below.

Omid_Motamedise_3-1724970516445.png



I hope this help you, if you have any other question, pls do not hesitate and ask

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

I realized I misrepresented my data and what I want my end product to look like. When I took a closer look at my data, I realized that there were mutliple rows that had responses in the same column. For this situations, I needed the value to be summed if it was a number and split by comma when it was text. 

 

Here is what my data actually looks like: 

GraceJinM_2-1725031402296.png

 

 

Here is what I want it to look like: 

GraceJinM_3-1725031402440.png

 

 

what happened on number 2 for the third rows on column 3 for section A?

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
AlienSx
Super User
Super User

@GraceJinM , check this out. Looks similar. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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