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:

PROB0001	01.02.2019	Marco Polo	WIP	Description1
PROB0002	04.05.2018	Vaso da Bosco	Assigned	Description2
PROB0002	06.07.2018	Marco Polo	WIP	Description3
PROB0002	06.07.2018	Katarina Valentina	WIP	Description4
PROB0003	04.09.2019	Louis de Pingpong	WIP	Description5
PROB0004	04.01.2018	Vaso da Bosco	WIP	Description6
PROB0004	07.03.2018	Louis de Pingpong	Assigned	Description7
PROB0004	07.03.2018	Louis de Pingpong	WIP	Description8

<tbody>
</tbody>

Ideal expected result:

PROB0001	01.02.2019	Marco Polo	WIP	Description1
PROB0002	04.05.2018	Vaso da Bosco	Assigned	Description2
PROB0002	06.07.2018	Marco Polo; Katarina Valentina	WIP; WIP	Description3; Description4;
PROB0003	04.09.2019	Louis de Pingpong	WIP	Description5
PROB0004	04.01.2018	Vaso da Bosco	WIP	Description6
PROB0004	07.03.2018	Louis de Pingpong; Louis de Pingpong	Assigned; WIP	Description7; Description8

<tbody>
</tbody>

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

Column1	Column2	Custom	Custom.1	Custom.2
PROB0001	01/02/2019​	Marco Polo	WIP	Description1
PROB0002	04/05/2018​	Vaso da Bosco	Assigned	Description2
PROB0002	06/07/2018​	Marco Polo;Katarina Valentina	WIP;WIP	Description3;Description4
PROB0003	04/09/2019​	Louis de Pingpong	WIP	Description5
PROB0004	04/01/2018​	Vaso da Bosco	WIP	Description6
PROB0004	07/03/2018​	Louis de Pingpong;Louis de Pingpong	Assigned;WIP	Description7;Description8

[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.