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.

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