Forum Discussion
How do I keep what was the column headers after transposing a table
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?
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
5 Replies
- MFelix
Super User
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
Unpromote headers and then transpose...
"Unpromote headers and then transpose" - sort out a similar problem I had.
To unpromote headers, the command "Use Header as First Row" under "Use First Row as Headers" can help.
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.
