Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Wasfiathhar
Frequent Visitor

Re Order Colulmn in Power Query

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

 

https://docs.google.com/spreadsheets/d/1rOBFeUPnJUqVNNOl93TJadfnRcvyv5va/edit?usp=sharing&ouid=11179... 

 

Current Data

 

Wasfiathhar_0-1662483089951.png

 

Desired Output

 

Wasfiathhar_1-1662483089957.png

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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

vjingzhang_0-1662519450154.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

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

vjingzhang_0-1662519450154.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors