Forum Discussion
Solved
Convert Multiple rows with duplicate into unique single row
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 Actio...
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"
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"
