How do I keep what was the column headers after transposing a table

[nerdlump]

I am tring to transpose a table from this:

Brand    | Purchase intent 1 | Purchase intent 2 | Purchase intent 3 

Brand 1 |100                        | 95                         | 65

Brand 2 | 99                         | 85                         | 45

Brand 3 | 88                         | 65                         | 15

To this:

Brand                   | Brand 1 | Brand 2 | Brand 3

Purchase intent 1 | 100       | 99          | 88

Purchase intent 2 | 95         | 85          | 65

Purchase intent 3 | 65         | 45          | 15

I am doing this by editing the query, and performing the following steps:

1) Transpose

2) Use First Row as Headers

But after doing this the row labels are missing, the result is:

Brand 1 | Brand 2 | Brand 3

100       | 99          | 88

 95         | 85          | 65

 65         | 45          | 15

How do I keep the first column of labels: Purchase intent 1, 2, 3?

[Anonymous]

Posting an answer to this in case someone else needs to transpose a .json with a dynamic set of column names.

Add a custom column with some text, i used "Pivot".

Right click your new column and then "Unpivot other columns".

Then you can delete your new column and your column headers will now be transposed into a column for you.

Voila. 

[MFelix · Accepted solution]

Hi @nerdlump ,

You need to use the Unpivot / Pivot options:

• Select the 3 Purchase columns
• Unpivot
• Select the Brand Column
• Pivot - By Value

Check the M code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKzEtRMFTSUTI0MACSlqZAwsxUKVYHJmcEErUEEhYgKRNkKWOQqAVEA9AAoFQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Brand = _t, #"Purchase Intent 1" = _t, #"Purchase Intent 2" = _t, #"Purchase Intent 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Purchase Intent 1", Int64.Type}, {"Purchase Intent 2", Int64.Type}, {"Purchase Intent 3", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Purchase Intent 1", "Purchase Intent 2", "Purchase Intent 3"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Only Selected Columns", List.Distinct(#"Unpivoted Only Selected Columns"[Brand]), "Brand", "Value", List.Sum)
in
    #"Pivoted Column"

Regards,

MFelix

[Chamudi]

Unpromote headers and then transpose...

[nerdlump]

Thanks @MFelix , your suggested solution worked a charm 🙂 thanks for helping me out with my problem area.