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
fiorela
Helper I
Helper I

how to summarize data, count category variable

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!

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

appreciate your help. many thanks !

parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

 many thanks!! it works

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.