Convert Multiple rows with duplicate into unique single row

[murali5431]

Hi,

I have several rows of data in my dataset. Each row duplicates since I have to consider two types of Action "A" & "L". This causes the row volume to double, as below:

Code	Name	Action	ID
ABC	Peter	A	D100
ABC	Peter	L	D200
ABD	Charlie	A	D150
ABD	Charlie	L	D250
ABE	Jack	A	D110
ABE	Jack	L	D220

Is there a way I can bring the data into single rows using power query, as in table below?

Code	Name	Action	ID	Action1	ID1
ABC	Peter	A	D100	L	D200
ABD	Charlie	A	D150	L	D250
ABE	Jack	A	D110	L	D220

Thanks in advance!

Muralidhar

[jennratten · Accepted solution]

First pivot the Action and ID columns.  Then unpivot each separately.

PIVOT

UNPIVOT

RESULT

SCRIPT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRCkgtSS0C0o5A7GJoYKAUq4Mu5QOSMoJLuQC5zhmJRTmZqXB9ptglITrhkq5ArldicjZcmyEWGYgeI6BMLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Name = _t, Action = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}, {"Action", type text}, {"ID", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Action]), "Action", "ID"),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Pivoted Column", {"A"}, "Attribute", "Value"),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Unpivoted Only Selected Columns", {"L"}, "Attribute.1", "Value.1")
in
    #"Unpivoted Only Selected Columns1"

[murali5431]

@jennratten Thanks a lot for the detailed explanation! It did help!!

Regards,

Muralidhar