Forum Discussion

bdoucet's avatar
bdoucet
Regular Visitor
8 years ago
Solved

CountIf with Group by in Power Query

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