Special Unpivot Columns

[johnmpl]

Hi, to all!

I need to special unpivot some data.

Input:

NAMEA	NAMEB	NAMEC	DATE THING 1	DESCRIPTION	DATE THING 2	DESCRIPTION 2	DATE THING 3	DESCRIPTION 3
Name1	Name28	Name55	28/01/2019	Descript 1	24/02/2019	Descript 28	23/03/2019	Descript 55
Name2	Name29	Name56	29/01/2019	Descript 2			24/03/2019	Descript 56
Name3	Name30	Name57	30/01/2019	Descript 3	26/02/2019	Descript 30
Name4	Name31	Name58	31/01/2019	Descript 4
Name5	Name32	Name59			28/02/2019	Descript 32	27/03/2019	Descript 59

<colgroup><col span="3"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Output:

NAMEA	NAMEB	NAMEC	TYPE	DATE	DESCRIPTION
Name1	Name28	Name55	THING 1	28/01/2019	Descript 1
Name1	Name28	Name55	THING 2	24/02/2019	Descript 28
Name1	Name28	Name55	THING 3	23/03/2019	Descript 55
Name2	Name29	Name56	THING 1	29/01/2019	Descript 2
Name2	Name29	Name56	THING 3	24/03/2019	Descript 56
Name3	Name30	Name57	THING 1	30/01/2019	Descript 3
Name3	Name30	Name57	THING 2	26/02/2019	Descript 30
Name4	Name31	Name58	THING 1	31/01/2019	Descript 4
Name5	Name32	Name59	THING 2	28/02/2019	Descript 32
Name5	Name32	Name59	THING 3	27/03/2019	Descript 59

<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

Appreciate all your help. Blessings!

 

[sandy666]

NAMEA	NAMEB	NAMEC	Type	Date	Description
Name1	Name28	Name55	THING 1	28/01/2019​	Descript 1
Name1	Name28	Name55	THING 1	28/01/2019​	Descript 28
Name1	Name28	Name55	THING 1	28/01/2019​	Descript 55
Name1	Name28	Name55	THING 2	24/02/2019​	Descript 1
Name1	Name28	Name55	THING 2	24/02/2019​	Descript 28
Name1	Name28	Name55	THING 2	24/02/2019​	Descript 55
Name1	Name28	Name55	THING 3	23/03/2019​	Descript 1
Name1	Name28	Name55	THING 3	23/03/2019​	Descript 28
Name1	Name28	Name55	THING 3	23/03/2019​	Descript 55
Name2	Name29	Name56	THING 1	29/01/2019​	Descript 2
Name2	Name29	Name56	THING 1	29/01/2019​	Descript 56
Name2	Name29	Name56	THING 3	24/03/2019​	Descript 2
Name2	Name29	Name56	THING 3	24/03/2019​	Descript 56
Name3	Name30	Name57	THING 1	30/01/2019​	Descript 3
Name3	Name30	Name57	THING 1	30/01/2019​	Descript 30
Name3	Name30	Name57	THING 2	26/02/2019​	Descript 3
Name3	Name30	Name57	THING 2	26/02/2019​	Descript 30
Name4	Name31	Name58	THING 1	31/01/2019​	Descript 4
Name5	Name32	Name59	THING 2	28/02/2019​	Descript 32
Name5	Name32	Name59	THING 2	28/02/2019​	Descript 59
Name5	Name32	Name59	THING 3	27/03/2019​	Descript 32
Name5	Name32	Name59	THING 3	27/03/2019​	Descript 59

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAMEA", type text}, {"NAMEB", type text}, {"NAMEC", type text}, {"DATE THING 1", type datetime}, {"DESCRIPTION", type text}, {"DATE THING 2", type datetime}, {"DESCRIPTION 2", type text}, {"DATE THING 3", type datetime}, {"DESCRIPTION 3", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"DATE THING 1", "DATE THING 2", "DATE THING 3"}, "Attribute", "Value"),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Unpivoted Only Selected Columns", {"DESCRIPTION", "DESCRIPTION 2", "DESCRIPTION 3"}, "Attribute.1", "Value.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns1",{{"Attribute", "Type"}, {"Value", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Value.1", "Description"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","DATE ","",Replacer.ReplaceText,{"Type"})
in
    #"Replaced Value"[/SIZE]

now you can sort / filter / remove duplicates from description or whatever you want

 

[johnmpl]

Thx Sandy for your answer.

If you remove duplicates, Descript28 is related to THING 1, when this must be in THING 2. Same thing with Descript55, must be with THING 3. How can I get the correct result? Blessings!

 

[sandy666]

you need to try yourself with columns selection and remove duplicates
I did unpivot as you wanted but the rest is up to you

remove duplicates from Description only is not mandatory