+ Reply to Thread
Results 1 to 5 of 5

Countifs function in Power Query?

  1. #1
    Registered User
    Join Date
    10-09-2003
    Posts
    25

    Countifs function in Power Query?

    Needed help on similar countifs function in Power Query.

    In the attached spreadsheet, Table1 is my raw data containing student name, module, course name, course status and module completion %. Each module may contain more than 1 courses and module completion % is based on the number of completed courses within the module.

    Table2 is the end result after going through Power Query GroupBy function on Name and Module Completion % column.

    I wanted to do a countifs i.e. to count the number of modules completed by student within Power Query Add Column but was not able to do so.

    Please help to point out where did I go wrong. Thanks.
    Attached Files Attached Files
    Last edited by slc; 04-23-2019 at 08:21 AM. Reason: To change to Solved status.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Countifs function in Power Query?

    Not sure if this is what you are looking for

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Countifs function in Power Query?

    I'm pretty sure I know what you're looking for...
    The count of completed modules per student.

    Using this M-Code:
    Please Login or Register  to view this content.
    These are the results:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Countifs function in Power Query?

    If you want the Module count and the count of completed modules per student.

    Try this M-Code:
    Please Login or Register  to view this content.
    These are the results:
    Please Login or Register  to view this content.

    Is that something you can work with?

  5. #5
    Registered User
    Join Date
    10-09-2003
    Posts
    25

    Re: Countifs function in Power Query?

    Alan,
    Thanks. Works for me. Did not occur to me to filter for 100 within the PQ.

    Ron,
    Yes. You summed it all. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Query NETWORKDAYS function
    By kersplash in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 12-13-2019, 06:42 PM
  2. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  5. Replies: 2
    Last Post: 04-03-2018, 08:47 PM
  6. [SOLVED] Replacement for this COUNTIFS formula using Power Query?
    By 4am in forum Excel General
    Replies: 0
    Last Post: 03-26-2018, 02:54 PM
  7. Power Query - Invoke Custom Function
    By leukep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2017, 02:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1