CountIf with Group by in Power Query

[bdoucet]

Hello, 

I've seen a few threads on this but cannot find an answer for my problem 

I have a table of visits and each row is a unique visit. Currently it's displaying all of the detail for each visit.

My goal is to group the data by Department then visit date, with custom columns that have aggregated data based on countif logic.

I have no problem loading it into powerquery and performing a group by with Department then Visit date, and adding a count of visits field. 

I added a second custom field using "All Rows" and have tried manipulating the M code to extract the data from the table with no luck. 

I have a field in the source data that shows whether a visit was verified or not and I'd like to add a custom column here that does a CountIF to count all of the rows whwere the Visit status was "Verified". So it will show the number of Verified visits by department and day.

Do I need to use Table.RowCount?

Thank you for any feedback.

--Brian

[Ashish_Mathur]

Hi @bdoucet,

Share some data and show the expected result.

[bdoucet]

Thank you Ashish.

The first screenshot is the raw data. It's a daily visit file with the VisitID being unique so each row is a single visit. The end result should be a group by DepartmentID and Visit date, with a col

The goal is to group the data by department then visit date, with a column that shows total visits (count of all rows), then two custom columns. See example below. 

1.) Count of rows where VisitStatus = "Verified"

2.) Count of rows where CoverageVRFStatus = "E-Verified"

Afterwards, I'm giong to create two measures in PowerPivot that divides these two new columns by total visits to get:

1.) Percentage of Visits that were Verified

2.) Percentage of Visits where a coverage was E-Verified

Which would be: SUM[Total Visits]

then: DIVIDE [Total Visits],[Verified Visits] etc.

[Ashish_Mathur]

Hi @bdoucet,

Share the link from where i can download your file.

[bdoucet]

Hi Ashish,

Excuse me for being ignorant, but I'm not sure how to attach a link to the file. The file is saved on my local desktop and it doesn't appear that I can attach it through the link in the forum. How can I get the file to you for review?

[bdoucet · Accepted solution]

I found a solution. 

Before aggregating, I added a custom column with a formula like:

= 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.

Appears to be a simple solution that's working quite well. 

Thanks to everyone for their suggestions.

[bdoucet]

I'll make one more note:

I know I can do this in DAX by using CALCULATE. To do so I would be importing all of the raw data, and I'm purposely trying not to do that. The detail of the data isn't necessary in this situation and I'm planning for the long term because this data model is going to be quite large.

My model is currently using PowerQuery to pick up all of the visit files from a folder and this group by is par tof the ETL process before I load it into PowerPivot.

Thanks very much for the feedback.

[v-shex-msft]

Hi @bdoucet,

You can refer to below steps to get the condition count of specific columns in query editor.

1. Create variable table VRF and Visit.

VRF

= Table.Group(Table.SelectRows(Table.SelectColumns(Source,{"Department","Date","CoverageVRF"}),each [CoverageVRF]="E-Verified"), {"Department", "Date"}, {{"Count CoverageVRF", each Table.RowCount(_), type number}})

VRF

Visit

= Table.Group(Table.SelectRows(Table.SelectColumns(Source,{"Department","Date","Visit"}),each [Visit]="Verified"), {"Department", "Date"}, {{"Count Visit", each Table.RowCount(_), type number}})

Visit

2. Join table to add count columns.

= Table.Join(Source,{"Department","Date"},Table.Join(Visit,{"Department","Date"},VRF,{"Department","Date"}),{"Department","Date"})

Result

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVfLahtBEPwVMWcHdt4z5+BrMASSg/DBTjZEFwuMIPjvo1nL2dre6Z7OQUiWyrX9qK7pOR6NNXemXl/BZZ+u79/m19Ov0/zz+vH+078/Hu+OxjXUDVm3yM/nl8vTj8vh4ent/LqA/fXb2AcT2kZpJ1UEjdDfkFlEJiQtIrQRlT7pPq0GtO/gMIm0LekMyC/znz1oCTH2H/7x8fD8dnj4fX6Z3/+jPT1pw7VurZgQhYdq0bSYMMJaiEF/bVwrMeiaTXrWzHcCQ/4+P389XW5BF77VTJpVqAwdjwnY23sHYqHdg2FzPLQfq2tddKNeu7CilLzYv0HQabUICIHhzeA8t3rtFewKxEsGuYOuICBVdh67Vgche4u2IigIVedhBgf69+iasmv5QNQmQCOUUCX7pXRoGwJ51k/IEmfQVaKu4hiwLj8z+uynFzZtLN2mYweDM+ojKnh16UJQG2OI6sqFZLQHesi8MvezFQqEOzjSKwm3a0Vxgsen0eOjVdtQdGZ79gpQD5EOWGHieH+NkbiaQJjM1rDl/DPxVoG48MPDaTxW4ifd7NJE3LgDsUAkVzQ58x+rV1q2FNUMJNwplwr0GXGfHG60qSWTjKazCXZKXiqp6BOCc23Q/Dxpnp0tPHuwHwt7P4XiIkmxnPJy0Kl19e+MfUtdFeb2NbNY71ub84oWaoariDY5uAZ8hMphywSFlntS8NYm96Q4w57+FIpmOGDF/V+e8gKOONBuSXpW5f6/qqYUo93DCr28ieqpk+G3QnortIb1fAp1pG4diDfas77ClFFxcYKs+vt23Vy4Ze1UoXWd6sJhNjbpWmkczL2buuSoHI9/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Department = _t, Date = _t, Visit = _t, CoverageVRF = _t]),
    VRF = Table.Group(Table.SelectRows(Table.SelectColumns(Source,{"Department","Date","CoverageVRF"}),each [CoverageVRF]="E-Verified"), {"Department", "Date"}, {{"Count CoverageVRF", each Table.RowCount(_), type number}}),
    Visit = Table.Group(Table.SelectRows(Table.SelectColumns(Source,{"Department","Date","Visit"}),each [Visit]="Verified"), {"Department", "Date"}, {{"Count Visit", each Table.RowCount(_), type number}}),
    Custom1 = Table.Join(Source,{"Department","Date"},Table.Join(Visit,{"Department","Date"},VRF,{"Department","Date"}),{"Department","Date"})
in
    Custom1

Regards,

Xiaoxin Sheng

[sun-and-sea]

Hi Xiaoxin,

I modified your formula for a use case of mine where I need to group by all the publications (count total publications) but also count the number of values where Clicked = 1. It did return the results but I wasn't sure if it's working correctly. Can you confirm?

= Table.Group(Table.SelectRows(Table.SelectColumns(#"External contacts only",{"OnePlace__Publication__c","Clicked__c"}),each [Clicked__c]=1), {"OnePlace__Publication__c"}, {{"Count Clicked__c", each Table.RowCount(_), type number}})

Thank you,

Kara

[parry2k]

why not filter by visit status first and then do the group by.

or this can be achieve by dax.

[bdoucet]

Hi Parry2k, 

Appreciate your response. The reason why I'm not filtering by Visit status first then doing a group by is because I'm using the countif for visitis verified to then divide by total visits to get a percentage of total visits verified.

I have several other fields besides the visit status that I'm looking to do this for.