Re Order Colulmn in Power Query

[Wasfiathhar]

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

Desired Output

[v-jingzhang · Accepted 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.