Convert Multiple rows with duplicate into unique single row

Question

Hi,&nbsp;I have several rows of data in my dataset. Each row duplicates since I have to consider two types of Action "A" &amp; "L". This causes the row volume to double, as below:&nbsp;CodeNameActionIDABCPeterAD100ABCPeterLD200ABDCharlieAD150ABDCharlieLD250ABEJackAD110ABEJackLD220&nbsp;Is there a way I can bring the data into single rows using power query, as in table below?&nbsp;CodeNameActionIDAction1ID1ABCPeterAD100LD200ABDCharlieAD150LD250ABEJackAD110LD220&nbsp;Thanks in advance!Muralidhar

jennratten · Accepted Answer

First pivot the Action and ID columns.&nbsp; Then unpivot each separately.&nbsp;PIVOTUNPIVOT&nbsp;RESULTSCRIPTlet
    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"&nbsp;

Forum Discussion

Convert Multiple rows with duplicate into unique single row

2 Replies

Related content

Duplicate ranking

remove duplicate value within a row

Remove duplicates based on multiple columns

Removing Duplicates

Duplicating the same Rule across multiple goals and sub goals

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