Forum Discussion

JessieWulala's avatar
JessieWulala
New Member
9 years ago

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...
Need Help
DallasSalazar's avatar
DallasSalazar
Frequent Visitor
9 years ago

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

 

  • JessieWulala's avatar
    JessieWulala
    New Member
    9 years ago

    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.

    • DallasSalazar's avatar
      DallasSalazar
      Frequent Visitor
      9 years ago

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

       

      All the best, my friend. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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"]
            }) 
 
        ) 
 
    ) 
 
)




      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.




      Thanks,
      Lydia Zhang