Unpivot or Transpose Header in row level into Column also values

One way to do this is to;

1) group by the first column choosing do not aggregate so you get all the rows

2) add an index column to the resulting nested tables

3) expand the nested tables

4) pivot the table on the first column, choosing not to aggregate the values in the second column

5) remove the index column

Here is an example code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouLUotzlCK1YlWCk7MSS0GChkagLlQBUGJWSiyRiiywaV5mTko8sZA+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"All", each Table.SelectColumns(_, {"Column2"}), type table [Column1=text, Column2=text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All", each Table.AddIndexColumn(_, "index", 1, 1)}}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"Column2", "index"}, {"Column2", "index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded All", List.Distinct(#"Expanded All"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"index"})
in
    #"Removed Columns"

Hi ssk_1984, another solution:

 

Before

 

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouLUotzlCK1YlWCk7MSS0GChkagLlQBUGJWSiyRiiywaV5mTko8sZA+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Transformed = Table.FromRows(List.Split(Source[Column2], 2), {"Name", "Sales"})
in
    Transformed

Hi ssk_1984 

 

another solution

let
Source = Your_Source,
Group = Table.Group(Source, {"Column1"}, {{"Data", each [Column2]}}),
Table = Table.FromColumns(Group[Data], Group[Column1])
in
Table

Stéphane 

Hi ssk_1984, try this:

 

Ouput

 

Procedure