Home Forums Power Pivot CountIf with Group by in Power Query

This topic contains 3 replies, has 2 voices, and was last updated by  bdeuce14 9 months, 4 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #8670

    bdeuce14
    Participant
    • Started: 11
    • Replies: 14
    • Total: 25

    I’m working with a file that has daily data for clinic visits. Each row in the report is a unique visit. I have been working on grouping the data by department ID and then the date so I can show aggregated totals for each day by department for specific metrics.

    I loaded the data into Power Query and used the Group by function to group by DepartmentID and VisitDate and added a count of rows and a table for all rows as seen below.

     

    GroupBy_1.jpg

     

    What I’d like to do is add a few more custom columns that count the number of rows based on a specific value in a column.

    For example, there’s a field called VisitStatus that indicates whether a visit was verified. I’d like to add a custom column that does a CountIF to show the total number of visits where the VisitStatus =”Verified” as seen in the screenshot below.

    GroupByFinal.png

    I attached a copy of my working file. I’m not sure which M functions to use, such as Table.RowCount or Text.Contains etc.

    Attachments:
    You must be logged in to view attached files.
    #8672

    bdeuce14
    Participant
    • Started: 11
    • Replies: 14
    • Total: 25

    To clarify, I know that I can load this into PowerPivot and use CALCULATE but I’d like to aggregate these before I load the data into the data model.

     

    #8681

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    Before aggregating, add a custom column with a formula like (I added a step to reorder the columns to make the grouping appear more intuitive):

    = Table.AddColumn(#”Reordered Columns”, “IsVerified”, each if [VisitStatus] = “Verified” then 1 else 0)

    Then when grouping, I added another column to the grouping that made a sum of the custom column.

    Please see attached workbook.

    Attachments:
    You must be logged in to view attached files.
    #8687

    bdeuce14
    Participant
    • Started: 11
    • Replies: 14
    • Total: 25

    Thank you Tom. What a simple and effective solution.

    I posted this same question on the form at PowerBI.com a few weeks ago because I wasn’t able to add a new message here, and you should have seen the overly complex suggestions I received.

    This will work well for my situation.

     

    Thanks again,

    Brian

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.