Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nerdlump
Frequent Visitor

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?

 

1 ACCEPTED SOLUTION
MFelix
Super User
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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Super User
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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Unpromote headers and then transpose...

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors