Results 1 to 6 of 6

Thread: Need help with Cumulative Count in PowerQuery

  1. #1
    Acolyte Iloveexcel&excelloveme's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2016

    Need help with Cumulative Count in PowerQuery



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Folks,

    I need some help to write a formula for cumulative count within PowerQuery, is just like Countif in excel. I can't do it in Excel as I've more than 500k of data

    • Click image for larger version. 

Name:	Screen Shot 2017-07-02 at 2.17.51 PM.png 
Views:	27 
Size:	76.9 KB 
ID:	7045

    This is the sample data

    Click image for larger version. 

Name:	Screen Shot 2017-07-03 at 8.57.44 PM.png 
Views:	29 
Size:	49.9 KB 
ID:	7046
    I need to do a cumulative count, using "Product | Category" as the criteria. The above is what i wanna achieve with " Column Header"

    Appreciate any help I've attached the original file for reference
    Attached Files Attached Files

  2. #2
    Acolyte Comfy's Avatar
    Join Date
    Oct 2016
    Posts
    61
    Articles
    0
    Excel Version
    2019
    Is the column concatenation mandatory?

    You can group by all four columns to return a count.
    Attached Files Attached Files

  3. #3
    Acolyte Iloveexcel&excelloveme's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2016
    Thanks Comfy,
    I can't Group all 4 as I need to Pivot "ProductID | Description".
    And i tried it earlier in the morning but it returned the sum count(e.g. 2) instead of a running order(e.g. 1,2,3). I need the running order as that will be the column header for "ProductID | Description"
    Click image for larger version. 

Name:	Screen Shot 2017-07-02 at 2.18.02 PM.png 
Views:	21 
Size:	47.8 KB 
ID:	7049Click image for larger version. 

Name:	Screen Shot 2017-07-02 at 2.18.02 PM.png 
Views:	21 
Size:	47.8 KB 
ID:	7049
    This is the end result i wanna achieve

  4. #4
    Acolyte Comfy's Avatar
    Join Date
    Oct 2016
    Posts
    61
    Articles
    0
    Excel Version
    2019
    Oops my mistake, forgot to add an additional step.

    I've attached the correct one.
    Attached Files Attached Files

  5. #5
    Acolyte Comfy's Avatar
    Join Date
    Oct 2016
    Posts
    61
    Articles
    0
    Excel Version
    2019
    All you will need to do is add a step to change the column Types (ID will need to be whole number)

    Then you'll be able to pivot.

  6. #6
    Acolyte Iloveexcel&excelloveme's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2016

    Solved

    Yes, this work like a charm, i did some slight changes to fit my dataset
    Last edited by Iloveexcel&excelloveme; 2017-07-04 at 05:56 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •