Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
I have been stuck on the power Query at this point.
Expert I need your support over here. provide some tips how i can reoder the column / row
Current Data
Desired Output
Solved! Go to Solution.
Hi @Wasfiathhar
Here is my transformation code. You can create a blank query, open its Advanced Editor and clear the code there. Paste below code into it and click Done to save the result. Then click steps in Applied Steps pane to see the result of each step.
In the last step, I append three tables. The first 2 tables is a simple result of "Select columns > Remove blank rows > Rename columns (Table2)". The 3rd table is a result of "Select columns > Remove blank rows > Promote first row to headers" > Unpivot two columns > Rename columns".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVDBbsIwDP0Vq2cYaVCGxA2KdhtlowemikMQZkS0SZW6m/r3c+gKSCWy4ue8J+fZeR5tz6Yq0VI0iqRUQrwyWGFhftC3DGOpplIwCLEf5VEHU39Ez1lJwWdAv2tCb3TBcLeDWM3kQJI0NbkSPax1iVwvlgkkjkHmSBdgm/LA5Ml5oDNC/e9yfhN8NNqSofaJ5P5JFzGMQQp4CzPGavrCjiFLH2R9r44P3N1tvx+gtgo+08mS7/BaGftdOXNd3Sb9zAYjrrAmYzUZZ7la4y98OX8ZyDaFvvbYou65/R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Select Columns 1&2" = Table.SelectColumns(#"Changed Type",{"Column1", "Column2"}),
Table1 = Table.SelectRows(#"Select Columns 1&2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Select Columns 3&4" = Table.SelectColumns(#"Changed Type",{"Column3", "Column4"}),
Table2 = Table.RenameColumns(Table.SelectRows(#"Select Columns 3&4", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), {{"Column3", "Column1"}, {"Column4", "Column2"}}),
#"Select Columns 5&6" = Table.SelectColumns(#"Changed Type",{"Column5", "Column6"}),
#"Removed Blank Rows 3" = Table.SelectRows(#"Select Columns 5&6", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows 3", [PromoteAllScalars=true]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {}, "Attribute", "Value"),
Table3 = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Column1"}, {"Value", "Column2"}}),
CombineTable = Table.Combine({Table2, Table3, Table1})
in
CombineTable
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Wasfiathhar
Here is my transformation code. You can create a blank query, open its Advanced Editor and clear the code there. Paste below code into it and click Done to save the result. Then click steps in Applied Steps pane to see the result of each step.
In the last step, I append three tables. The first 2 tables is a simple result of "Select columns > Remove blank rows > Rename columns (Table2)". The 3rd table is a result of "Select columns > Remove blank rows > Promote first row to headers" > Unpivot two columns > Rename columns".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVDBbsIwDP0Vq2cYaVCGxA2KdhtlowemikMQZkS0SZW6m/r3c+gKSCWy4ue8J+fZeR5tz6Yq0VI0iqRUQrwyWGFhftC3DGOpplIwCLEf5VEHU39Ez1lJwWdAv2tCb3TBcLeDWM3kQJI0NbkSPax1iVwvlgkkjkHmSBdgm/LA5Ml5oDNC/e9yfhN8NNqSofaJ5P5JFzGMQQp4CzPGavrCjiFLH2R9r44P3N1tvx+gtgo+08mS7/BaGftdOXNd3Sb9zAYjrrAmYzUZZ7la4y98OX8ZyDaFvvbYou65/R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Select Columns 1&2" = Table.SelectColumns(#"Changed Type",{"Column1", "Column2"}),
Table1 = Table.SelectRows(#"Select Columns 1&2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Select Columns 3&4" = Table.SelectColumns(#"Changed Type",{"Column3", "Column4"}),
Table2 = Table.RenameColumns(Table.SelectRows(#"Select Columns 3&4", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), {{"Column3", "Column1"}, {"Column4", "Column2"}}),
#"Select Columns 5&6" = Table.SelectColumns(#"Changed Type",{"Column5", "Column6"}),
#"Removed Blank Rows 3" = Table.SelectRows(#"Select Columns 5&6", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows 3", [PromoteAllScalars=true]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {}, "Attribute", "Value"),
Table3 = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Column1"}, {"Value", "Column2"}}),
CombineTable = Table.Combine({Table2, Table3, Table1})
in
CombineTable
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.