Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Rookarumba
Helper III
Helper III

Get & Transform . How to write Cumulative Count with M language or within PowerQuery

Hi Folks,

 

I'm looking to do cumulative count within PowerQuery so i can use that particular column and Pivot it as a header.

 

Below in the sample Data before any queries.

Screen Shot 2017-07-02 at 2.17.51 PM.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I managed to merge Product & Category, Product ID & Product Description with " | " as the delimiter.
Then I created an extra column "Column Header" as the Header to Pivot as the Column Header for " Product ID & Product Description".
In excel the formula will be like =COUNTIF(M$4:M4,M4) to get a cumulative count for "Product & Category"
Column M is "Product | Category "Screen Shot 2017-07-03 at 8.57.44 PM.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I'm hoping someone can help me out with the formula cumulative count within PowerQuery, using "Product | Category" as the criteria.

1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

This is the same like a nested index. Please check if this video helps: https://www.youtube.com/watch?v=-3KFZaYImEY

You would group on column "Product | Category" and the added Index would be your cumulative count.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Community Champion
Community Champion

This is the same like a nested index. Please check if this video helps: https://www.youtube.com/watch?v=-3KFZaYImEY

You would group on column "Product | Category" and the added Index would be your cumulative count.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Yes, This work like a charm 🙂 I guess i didnt understand the grouping function as i only Group based on 1 of column 🙂

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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