Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |