Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
12 | |
11 | |
11 | |
8 |