cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JessieWulala Frequent Visitor
Frequent Visitor

format table visualization: transpose

I created a table visualization in Power BI, and it contains all the information I want. I just wish I can transpose it to make the format look better. Is there a way to achieve this?

 

For example my table looks like:

 

_______      subject1       subject2          subject2

student1    grade1_1     grade1_2         grade1_3

student2    grade2_1     grade2_2         grade2_3

 

what I want is:

__________   student1           student2

subject1     grade1_1          grade2_1

subject2     grade1_2          grade2_2

 

 

thank you.

7 REPLIES 7
DallasSalazar Frequent Visitor
Frequent Visitor

Re: format table visualization: transpose

Jesse, 

 

If you highlight the data you'd like to swap [for instance, it appears you want to swap the positioning of the top rows for the side columns --- which is a pretty common need]: 

 

1) simply highlight your data that you'd like to "swap" or move

2) copy the data or use CTRL+C

3) instead of trying to "paste" the data into the "swap" position [i.e. using CTRL+V] - click the "paste" BUTTON in the top left side of Excel

4) click the "transpose" button --- to which I've highlighted within the RED CIRCLE

 

Your data should pre-fill in vertical format [i.e. column] instead of horizontal [i.e. row]. 

 

I hope this was what you were looking for.

 

-Dallas

 

TRANSPOSE.png

JessieWulala Frequent Visitor
Frequent Visitor

Re: format table visualization: transpose

Thanks Dallas, but I cannot do this in Power BI. Sorry I didn't montion it is a Power BI task in my previous question.

Highlighted
DallasSalazar Frequent Visitor
Frequent Visitor

Re: format table visualization: transpose

Maybe consider tranposing your table in Excel prior to PBI upload? 

 

All the best, my friend. 

Moderator v-yuezhe-msft
Moderator

Re: format table visualization: transpose

Hi @JessieWulala,

In Power BI Desktop, you can use Table.Transpose function to transpose your table.

Please note that in Power BI Desktop, we are not able to create two columns with same name, thus I make a test using the following steps.

1. In Query Editor, click “New Source>Blank Query”, click Advanced Editor, then paste the following code.

let
    Table3 = Table.PromoteHeaders( 
 
    Table.Transpose( 
 
        Table.DemoteHeaders( 
 
            Table.FromRecords({ 
 
                [Column1 = "student1", subject1= "grade1_1"], 
 
                [Column1 = "student2", subject1= "grade2_1"]
            }) 
 
        ) 
 
    ) 
 
)



2. In Query Editor, click “New Source>Blank Query”, click Advanced Editor, then paste the following code.

Table.PromoteHeaders( 
 
    Table.Transpose( 
 
        Table.DemoteHeaders( 
 
            Table.FromRecords({ 
 
                [Column1 = "student1", subject2= "grade1_2"], 
 
                [Column1 = "student2", subject2= "grade2_2"]
            }) 
 
        ) 
 
    ) 
 
)


2.PNG

3. Select the first query, append the second query to the first query using “Appended Queries” button as shown in the following screenshot, then you will get the desired result.
1.PNG

3.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BryanSt Regular Visitor
Regular Visitor

Re: format table visualization: transpose

I had a similar issue and did a google search and looked at some of the responses.

 

In the end I stumbled across the Format option under Values "Show on Rows", which was set to Off as default.  Switched that and the format switched.  No addtional code.

Roseventura Member
Member

Re: format table visualization: transpose

Genius!  That's exactly what I needed!

 

Thank you!

NaiOh Frequent Visitor
Frequent Visitor

Re: format table visualization: transpose

Thank you.

This been bugging me for a while. Glad I found this post.

 

To add to this, the option found when using 'Matrix' in Visualizations, in format (paint roller).

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors