Combine values from multiple rows

[alagator28]

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!

[lbendlin · Accepted solution]

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.

[Omid_Motamedise · Accepted solution]

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!

[Anonymous]

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

[wdx223_Daniel]

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])

[alagator28]

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

[Omid_Motamedise · Accepted solution]

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!

[alagator28]

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!

[Omid_Motamedise]

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(_,", "))

[alagator28]

Thank you @Omid_Motamedise . I was able to use this code after finding duplicates in my "Name" column which was causing the issue. Works now.

[alagator28]

Thank you @Omid_Motamedise . I really appreciate the help. Still not working, so I'll try to explain as well as I can.

First, after the unpivot step, I don't get any null values. So, filtering them doesn't change anything. Thought it might be important to mention. Here is what it looks like after that step:

Once I get to the last step and I make the changes you suggest, I'm now getting errors in Created, Closed and Ticket columns.

The new errors I see everywhere look like it doesn't like that those values are in date format:

I went back and tried it with the DateTime being in text format and I end up with this, which doesn't quite work. I need them showing in the columns.

Here is the final code if it helps:

let
    Source = Sql.Database("***********", "***************"),
    dbo_CustomFieldDatas = Source{[Schema="dbo",Item="CustomFieldDatas"]}[Data],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"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", each Text.Combine(_,", "))
in
    #"Pivoted Column"

Any ideas? Thanks again!

[lbendlin · Accepted solution]

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.

[alagator28]

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

[lbendlin]

Doesn't look like you used my code.  Can you show what you modified?

[alagator28]

Hi @lbendlin , I used your code to the best of my ability. After I load the data from the source, there are steps I need to add to get my data looking like the example. Therefore, I grabbed your code starting from the #"Replaced Value" step.

Here is my complete code with confidential info removed.

let
    Source = Sql.Database("*******", "*********"),
    dbo_CustomFieldDatas = Source{[Schema="dbo",Item="CustomFieldDatas"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_CustomFieldDatas,{"FieldDefinitionName", "TicketId", "DateTimeValue", "StringValue", "ListItemName"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each 
(Text.Contains([FieldDefinitionName], "Billet Support **") and [StringValue] <> null and [StringValue] <> "") or 
(Text.Contains([FieldDefinitionName], "Raison Billet **") and [ListItemName] <> null and [ListItemName] <> "") or 
(Text.Contains([FieldDefinitionName], "Date/Heure de création du billet **") and [DateTimeValue] > #datetime(2000, 1, 1, 0, 0, 0)) or
(Text.Contains([FieldDefinitionName], "Date/Heure de fermeture du billet **") and [DateTimeValue] > #datetime(2000, 1, 1, 0, 0, 0))),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each [StringValue] <> "123" and [StringValue] <> "234" and [StringValue] <> "345"),
    #"Added Group column" = Table.AddColumn(#"Filtered Rows", "Group", each Text.AfterDelimiter([FieldDefinitionName], "** "), type text),
    #"Added Custom" = Table.AddColumn(#"Added Group column", "Type", each Text.Remove([FieldDefinitionName],{"0".."9"})),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "DataType", each if [FieldDefinitionName] = "Date/Heure de création du billet ** 1" then "Created" else if [FieldDefinitionName] = "Raison Billet ** 1" then "Reason" else if [FieldDefinitionName] = "Date/Heure de fermeture du billet MS 1" then "Closed" else "Ticket", type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"FieldDefinitionName", "Type"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ListItemName", "Reason"}, {"StringValue", "**Ticket"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"TicketId", "Group", "DataType", "**Ticket", "Reason", "DateTimeValue"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"**Ticket", "Ticket"}, {"DataType", "Name"}, {"DateTimeValue", "DateTime"}, {"TicketId", "ID"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","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"

Please let me know where I went wrong and how I can have multiple rows with the same ID and Ticket, Reason, Created and Closed values. Thanks.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[alagator28]

Hi @lbendlin . My post keeps disappearing after refreshing the page. Hoping it works this time...

This should cover it.

Thanks.

I'm trying to go from this:

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 01:00
asdf	1	Closed	null	null	2024-10-10 01:00
asdf	2	Ticket	555	null	null
asdf	2	Reason	null	why not	null
asdf	2	Created	null	null	2024-10-10 01:00
asdf	2	Closed	null	null	2024-10-12 01:00
xyz	1	Ticket	555	null	null
xyz	1	Reason	null	why not	null
xyz	1	Created	null	null	2023-01-01 01:00
xyz	2	Ticket	555	null	null
xyz	2	Reason	null	why not	null
xyz	2	Created	null	null	2024-10-10 01:00
xyz	2	Closed	null	null	2023-10-12 01:00

To this:

ID	Group	Ticket	Reason	Created	Closed
asdf	1	123	because	2024-10-10 01:00	2024-10-10 01:00
asdf	2	555	why not	2024-10-10 01:00	2024-10-12 01:00
xyz	1	555	why not	2023-01-01 01:00	null
xyz	2	555	why not	2024-10-10 01:00	2024-10-12 01:00

[lbendlin · Accepted solution]

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"

[alagator28]

Thank you, @lbendlin . I was able to resolve my issue after finding and correcting duplicates in my data in the "Name" column. I appreciate your follow-ups!