Transforming Dynamic Header into Row

Hi ngiam 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSwcDV1QqBhgq1tVCmEYJpjGCaIJjEqY3ViVYyNDQECrnn56Rk5qUDWQGJpTlAKig/KbWoBMhwzsxLqQTSJlBsBMTGaNgUahJILiKxLDO1CGRkUWJGYi6Q4QM0IRFI+2bmgdVC9BihmQnChkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, NAME1 = _t, NAME2 = _t, NAME3 = _t, NAME4 = _t, #"Able to drive well" = _t, #" " = _t, #" .1" = _t, #" .2" = _t, #"Able to articulate thoughts" = _t, #" .3" = _t, #" .4" = _t, #" .5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NAME1", type text}, {"NAME2", type text}, {"NAME3", type text}, {"NAME4", type text}, {"Able to drive well", type text}, {" ", type text}, {" .1", type text}, {" .2", type text}, {"Able to articulate thoughts", type text}, {" .3", type text}, {" .4", type text}, {" .5", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Removed Top Rows" = Table.Skip(#"Unpivoted Other Columns",4),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Attribute.1] = " " or [Attribute.1] = "Able to articulate thoughts" or [Attribute.1] = "Able to drive well") and ([ID] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Attribute.1]=" " then List.Max(Table.SelectRows(#"Added Index",(x)=>x[ID]=[ID] and x[Index]=
List.Max(Table.SelectRows(#"Added Index",(x)=>x[ID]=[ID] and x[Index]<[Index] and x[Attribute.1]<>" ")[Index]))[Attribute.1]) else [Attribute.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Attribute.1", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"ID", "Custom", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"ID", "Custom"}, {{"Count", each _, type table [ID=nullable number, Custom=text, Value=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns2", "Custom.1", {"Value", "Index"}, {"Value", "Index"}),
    Custom1 = Table.SelectRows(#"Removed Columns", each ([ID] <> null) and ([Attribute.1] <> " " and [Attribute.1] <> "Able to articulate thoughts" and [Attribute.1] <> "Able to drive well")),
    #"Split Column by Position" = Table.SplitColumn(Custom1, "Attribute.1", Splitter.SplitTextByPositions({0, 4}, false), {"Attribute.1.1", "Attribute.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1.1", type text}, {"Attribute.1.2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom.1", {"ID", "Index"},#"Changed Type2", {"ID", "Attribute.1.2"}, "Expanded Custom.1", JoinKind.LeftOuter),
    #"Expanded Expanded Custom.1" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom.1", {"Value"}, {"Value.1"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Expanded Custom.1",{"Index"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns3",{"ID", "Value.1", "Custom", "Value"})
in
    #"Reordered Columns1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.