Forum Discussion
Combine values from multiple rows
Hello, how can I combine values from multiple rows based on conditions? I need to extract values from every row where ID and Group are both the same as the other rows. Here is a simplified view of my data:
| ID | Group | Name | Ticket | Reason | DateTime |
| asdf | 1 | Ticket | 123 | null | null |
| asdf | 1 | Reason | null | because | null |
| asdf | 1 | Created | null | null | 2024-10-10 |
| asdf | 1 | Closed | null | null | 2024-10-11 |
| xyz | 3 | Ticket | 555 | null | null |
| xyz | 3 | Reason | null | why not | null |
| xyz | 3 | Created | null | null | 2023-01-01 |
I would like the result to be as follows:
| ID | Group | Ticket | Reason | Created | Closed |
| asdf | 1 | 123 | because | 2024-10-10 | 2024-10-11 |
| xyz | 3 | 555 | why not | 2023-01-01 | null |
I've been trying using the group by option, but I can't seem to get it perfect. Any advice would be welcome.
Thanks in advance!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOSVPSUTIE4pDM5OzUEhDHyBhI5pXm5MCoWB0UlUGpicX5eQg1SanJiaXFqThUOxelJpakpqAZqaNkZGBkomtoAEQYOnLyi/FpMARrqKisAgoaI7vc1NQUi8sRCtEdXp5RqZCXX4JdMR53G+saGAKRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Name = _t, Ticket = _t, Reason = _t, DateTime = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Ticket", "Reason", "DateTime"}), trans = (tbl)=> let #"Removed Other Columns" = Table.SelectColumns(Table.AddColumn(tbl, "Value", each List.RemoveNulls({[Ticket],[Reason],[DateTime]}){0}),{"Name", "Value"}) in Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Name]), "Name", "Value"), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID", "Group"}, {{"Rows", each trans(_), type table [ Ticket=nullable text, Reason=nullable text, Created=nullable text, Closed=nullable text]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Ticket", "Reason", "Created", "Closed"}, {"Ticket", "Reason", "Created", "Closed"}) in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Consider the next data in power query
Select the columns Ticket, Reason, and Date time then right click on one of them and pick unpivot columns to reach next image.
then on the value column filter non null values and also remove column Attribute to reach the next image
now select Name column and from transform tab pick pivot column and make the next setting to solve the problem.
the result would be like the next image
here you can find the whole code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOSVPSUTIE4pDM5OzUEhDHyBhI5pXm5MCoWB0UlUGpicX5eQg1SanJiaXFqThUOxelJpakpqAZqaNkZGBkomtoAEQYOnLyi/FpMARrqKisAgoaI7vc1NQUi8sRCtEdXp5RqZCXX4JdMR53G+saGAKRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Name = _t, Ticket = _t, Reason = _t, DateTime = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Group", "Name"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "null")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value") in #"Pivoted Column"If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
Looks like my original code produces that result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOSVPSUTIE4pDM5OzUEhDHyBhI5pXm5MCoWB0UlUGpicX5eQg1SanJiaXFqThUOxelJpakpqAZqaNkZGBkomtoAEQKBoZWBgZAhKE1J7+YdJ1GyJ4xNTXF7RkjLJ4pz6hUyMsvwaGaTM8YEeEZI1SdFZVV6BGD3S8IhUR4BaEYj0+MdQ0MgQibe4gIW4RCot1DTsgi6cQZsMYYARsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Name = _t, Ticket = _t, Reason = _t, DateTime = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Ticket", "Reason", "DateTime"}), trans = (tbl)=> let #"Removed Other Columns" = Table.SelectColumns(Table.AddColumn(tbl, "Value", each List.RemoveNulls({[Ticket],[Reason],[DateTime]}){0}),{"Name", "Value"}) in Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Name]), "Name", "Value"), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID", "Group"}, {{"Rows", each trans(_), type table [ Ticket=nullable text, Reason=nullable text, Created=nullable text, Closed=nullable text]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Ticket", "Reason", "Created", "Closed"}, {"Ticket", "Reason", "Created", "Closed"}) in #"Expanded Rows"
16 Replies
NewStep=Table.Combine(Table.Group(YourTable,{"ID","Group"},{"n",each Table.FromRecords({_{0}[[ID],[Group]]&Record.FromTable(#table({"Name","Value"},Table.ToList(_,each List.RemoveNulls(List.Skip(_,2)))))})})[n])
Thank you wdx223_Daniel . I followed your instructions and received an error.
Could it have to do with the fact that I have rows where the Id column is not unique?For example:
ID Group Name Ticket Reason DateTime asdf 1 Ticket 123 null null asdf 1 Reason null because null asdf 1 Created null null 2024-10-10 asdf 1 Closed null null 2024-10-11 asdf 2 Ticket 555 null null asdf 2 Reason null why not null asdf 2 Created null null 2023-01-01
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOSVPSUTIE4pDM5OzUEhDHyBhI5pXm5MCoWB0UlUGpicX5eQg1SanJiaXFqThUOxelJpakpqAZqaNkZGBkomtoAEQYOnLyi/FpMARrqKisAgoaI7vc1NQUi8sRCtEdXp5RqZCXX4JdMR53G+saGAKRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Name = _t, Ticket = _t, Reason = _t, DateTime = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Ticket", "Reason", "DateTime"}), trans = (tbl)=> let #"Removed Other Columns" = Table.SelectColumns(Table.AddColumn(tbl, "Value", each List.RemoveNulls({[Ticket],[Reason],[DateTime]}){0}),{"Name", "Value"}) in Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Name]), "Name", "Value"), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID", "Group"}, {{"Rows", each trans(_), type table [ Ticket=nullable text, Reason=nullable text, Created=nullable text, Closed=nullable text]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Ticket", "Reason", "Created", "Closed"}, {"Ticket", "Reason", "Created", "Closed"}) in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Thank you lbendlin . I followed your instructions and received an error.
Could it have to do with the fact that I have rows where the Id column is not unique? I need to allow duplicates in the ID column.For example:
ID Group Name Ticket Reason DateTime asdf 1 Ticket 123 null null asdf 1 Reason null because null asdf 1 Created null null 2024-10-10 asdf 1 Closed null null 2024-10-11 asdf 2 Ticket 555 null null asdf 2 Reason null why not null asdf 2 Created null null 2023-01-01 Doesn't look like you used my code. Can you show what you modified?
Consider the next data in power query
Select the columns Ticket, Reason, and Date time then right click on one of them and pick unpivot columns to reach next image.
then on the value column filter non null values and also remove column Attribute to reach the next image
now select Name column and from transform tab pick pivot column and make the next setting to solve the problem.
the result would be like the next image
here you can find the whole code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOSVPSUTIE4pDM5OzUEhDHyBhI5pXm5MCoWB0UlUGpicX5eQg1SanJiaXFqThUOxelJpakpqAZqaNkZGBkomtoAEQYOnLyi/FpMARrqKisAgoaI7vc1NQUi8sRCtEdXp5RqZCXX4JdMR53G+saGAKRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Name = _t, Ticket = _t, Reason = _t, DateTime = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Group", "Name"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "null")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value") in #"Pivoted Column"If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
Thank you, Omid_Motamedise . Although I got a few responses to this question, I chose yours since it allowed me to see what was happening step by step. The only problem I have after I have finished, I received errors for rows where the Id column is not unique. For example:
ID Group Name Ticket Reason DateTime asdf 1 Ticket 123 null null asdf 1 Reason null because null asdf 1 Created null null 2024-10-10 asdf 1 Closed null null 2024-10-11 asdf 2 Ticket 555 null null asdf 2 Reason null why not null asdf 2 Created null null 2023-01-01 The result seems to only return the results for the first group for ID: asdf.
When I view the error, I get:
Do you know a way around this?
Thanks!
Thank you, the error you have shared is about the rows with more than one item for a field. in such condition you can use the last parameters in Table.Pivot function which is now
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value")
but you can add some aggregation function in the last argument, so rewrite the previous formula as the next by adding the fifth argument to solve this problem
= Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value", each Text.Combine(_,", "))
Hi alagator28 ,
Thanks for all of your answers!
alagator28 I have tested everyone's solutions and they all seem to be fine. Please remember to accept their replies as solutions to help the other members find it more quickly if they can help you solve your problem.
Best Regards,
Dino Tao
