Forum Discussion

alicito1802's avatar
alicito1802
New Member
2 years ago
Solved

Create a transposed table from an existing table

I have the following table with 10 KPI but I am struggling to make a bar chart and an stacked bar chart with it, I did a manual table and found the solution that allow me to plot the data and get my ...
Need Help
  • alicito1802's avatar
    alicito1802
    2 years ago

    I solved like this:

    Copied the original table.

    In the copied table selected the 10 KPI columns and unpivot them.

    Renamed The Attribute Colum to KPI

    Clicked “Create New table” in the data model and used the summarize function as follow:

    SummarizedRAG =

    SUMMARIZE(

         'DB (3)',

         'DB (3)'[KPI],

         "RedCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Red")),

         "AmberCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Amber")),

         "GreenCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Green"))

    )

kiran_jadhav's avatar
kiran_jadhav
Frequent Visitor
2 years ago

Hello Alicito,
You can achive this using few transformation in Power query.

1. Remove the Promoted Headers  step from Applied steps. 

2. Select all the colums and Transpose.

3.Rename 1st column to KPI.

4. Select the KPI column and click on  Unpivot other Columns.
5. Select KPI & Value  column and  Remove Other Column.
6.Duplicate the KPI column.
7. select Value Column & Pivot it. (select KPi column as values).

You will have your desired result.
I have attached the screenshot for your referance.

 

 

 

 

  

 

 
 

 

 

 
 

 



  • alicito1802's avatar
    alicito1802
    New Member
    2 years ago

    Thanks for your reply Jadhav, it kind of worked but the numbers dont add up, for the HSE column I should be getting 25 green and one amber.

     

    And I am getting 34 gree and 1 amber