Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
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"
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User