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...
  • bdoucet's avatar
    bdoucet
    8 years ago

    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's avatar
bdoucet
Regular Visitor
8 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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