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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi bdoucet,

 

Share some data and show the expected result.

  • bdoucet's avatar
    bdoucet
    Regular Visitor
    8 years ago

    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.

    • 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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi bdoucet,

       

      Share the link from where i can download your file.

      • bdoucet's avatar
        bdoucet
        Regular Visitor
        8 years ago

        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?