how to summarize data, count category variable

[fiorela]

Hi all,

I have this type of table in my powerbi Desktop:

Type1                 Type2                 Type3               type4

intermediate       Intermediate      Advanced        Awareness 

Novice                Intermediate      Novice              Awareness

Advanced            Novice               Novice              Intermediate

Awareness           Novice               Novice              Advanced

and i would like to convert it into:

                   Novice     Intermediate        Advanced      Awareness

Type1               1               1                          1                     1

Type2              2                2                           0                     0

Type3

Type4

Thank you!

[parry2k · Accepted solution]

@fiorela solution attached, look at table(2), lot of heavy lifting is done in power query

- added index column

- unpivoted the table

and then everything was very easy

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, add an Index column.  Click on the Index heading and select "Unpivot Other olumns".  Build a matrix visual and drag Attribute to the row labels.  Drag the 3rd column to the column labels.  Write this measure

=COUNTROWS(Data)

Hope this helps.

[fiorela]

appreciate your help. many thanks !

[parry2k · Accepted solution]

@fiorela solution attached, look at table(2), lot of heavy lifting is done in power query

- added index column

- unpivoted the table

and then everything was very easy

[fiorela]

 many thanks!! it works

[Ashish_Mathur]

You are welcome.