Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 like
Here is what I want my data to look like:
What I want my data to look like
Thanks!
Solved! Go to Solution.
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
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
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.
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:
Here is what I want it to look like:
Hi @GraceJinM, different approach:
Output v1
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
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
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
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?
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
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:
Here is what I want it to look like:
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
Follow this step
this is your data in power query
right click on column section and use unpivot other columns to reach the next result
then select Attribute column and go to transform tab and pick Pivot column comand.
like the below image pic value for value column.
hit ok to reach your result as below.
I hope this help you, if you have any other question, pls do not hesitate and ask
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:
Here is what I want it to look like:
what happened on number 2 for the third rows on column 3 for section A?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
6 | |
6 |