Marge rows if specific conditions are met

[pitaszek]

Hi Guys,

It's is hard to describe what I need in subjects title. Maybe let me start with dataset:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PROB0001[/TD]
[TD]01.02.2019[/TD]
[TD]Marco Polo[/TD]
[TD]WIP[/TD]
[TD]Description1[/TD]
[/TR]
[TR]
[TD]PROB0002[/TD]
[TD]04.05.2018[/TD]
[TD]Vaso da Bosco[/TD]
[TD]Assigned[/TD]
[TD]Description2[/TD]
[/TR]
[TR]
[TD]PROB0002[/TD]
[TD]06.07.2018[/TD]
[TD]Marco Polo[/TD]
[TD]WIP[/TD]
[TD]Description3[/TD]
[/TR]
[TR]
[TD]PROB0002[/TD]
[TD]06.07.2018[/TD]
[TD]Katarina Valentina[/TD]
[TD]WIP[/TD]
[TD]Description4[/TD]
[/TR]
[TR]
[TD]PROB0003[/TD]
[TD]04.09.2019[/TD]
[TD]Louis de Pingpong[/TD]
[TD]WIP[/TD]
[TD]Description5[/TD]
[/TR]
[TR]
[TD]PROB0004[/TD]
[TD]04.01.2018[/TD]
[TD]Vaso da Bosco[/TD]
[TD]WIP[/TD]
[TD]Description6[/TD]
[/TR]
[TR]
[TD]PROB0004[/TD]
[TD]07.03.2018[/TD]
[TD]Louis de Pingpong[/TD]
[TD]Assigned[/TD]
[TD]Description7[/TD]
[/TR]
[TR]
[TD]PROB0004[/TD]
[TD]07.03.2018[/TD]
[TD]Louis de Pingpong[/TD]
[TD]WIP[/TD]
[TD]Description8[/TD]
[/TR]
</tbody>[/TABLE]

Ideal expected result:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PROB0001[/TD]
[TD]01.02.2019[/TD]
[TD]Marco Polo[/TD]
[TD]WIP[/TD]
[TD]Description1[/TD]
[/TR]
[TR]
[TD]PROB0002[/TD]
[TD]04.05.2018[/TD]
[TD]Vaso da Bosco[/TD]
[TD]Assigned[/TD]
[TD]Description2[/TD]
[/TR]
[TR]
[TD]PROB0002[/TD]
[TD]06.07.2018[/TD]
[TD]Marco Polo; Katarina Valentina[/TD]
[TD]WIP; WIP[/TD]
[TD]Description3; Description4;[/TD]
[/TR]
[TR]
[TD]PROB0003[/TD]
[TD]04.09.2019[/TD]
[TD]Louis de Pingpong[/TD]
[TD]WIP[/TD]
[TD]Description5[/TD]
[/TR]
[TR]
[TD]PROB0004[/TD]
[TD]04.01.2018[/TD]
[TD]Vaso da Bosco[/TD]
[TD]WIP[/TD]
[TD]Description6[/TD]
[/TR]
[TR]
[TD]PROB0004[/TD]
[TD]07.03.2018[/TD]
[TD]Louis de Pingpong; Louis de Pingpong[/TD]
[TD]Assigned; WIP[/TD]
[TD]Description7; Description8[/TD]
[/TR]
</tbody>[/TABLE]

If for specific Problem ID the same date appears certain amount of times, it should marge the values for all columns. So first it looks if the problem ID is the same and than looks for dates that occur more then once to merge it.

The wider which I need that for is to create appointments for specific date for specific problem.

Thank you in advance!

Regards,
Witek

 

[Kerryx]

Wouldn't a simple pivot table do this for you?

 

[sandy666]

try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Custom[/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Custom.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]PROB0001[/td][td=bgcolor:#E2EFDA]

01/02/2019​

[/td][td=bgcolor:#E2EFDA]Marco Polo[/td][td=bgcolor:#E2EFDA]WIP[/td][td=bgcolor:#E2EFDA]Description1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PROB0002[/td][td]

04/05/2018​

[/td][td]Vaso da Bosco[/td][td]Assigned[/td][td]Description2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]PROB0002[/td][td=bgcolor:#E2EFDA]

06/07/2018​

[/td][td=bgcolor:#E2EFDA]Marco Polo;Katarina Valentina[/td][td=bgcolor:#E2EFDA]WIP;WIP[/td][td=bgcolor:#E2EFDA]Description3;Description4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PROB0003[/td][td]

04/09/2019​

[/td][td]Louis de Pingpong[/td][td]WIP[/td][td]Description5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]PROB0004[/td][td=bgcolor:#E2EFDA]

04/01/2018​

[/td][td=bgcolor:#E2EFDA]Vaso da Bosco[/td][td=bgcolor:#E2EFDA]WIP[/td][td=bgcolor:#E2EFDA]Description6[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PROB0004[/td][td]

07/03/2018​

[/td][td]Louis de Pingpong;Louis de Pingpong[/td][td]Assigned;WIP[/td][td]Description7;Description8[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1", "Column2"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Column3")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Column([Count],"Column4")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.Column([Count],"Column5")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Custom.2", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
in
    #"Extracted Values2"[/SIZE]

 

[pitaszek]

Wouldn't a simple pivot table do this for you?

Hi Kerryx, thanks for you reply. Pivot won't help my in my scenario unfortunately. This is a part of a bigger automation. Pivot brings some limitations.