powerquerycountif

How do I replicate COUNTIF in PowerQuery, to count the number of date stamps in column 2 against each reference in column 1

I have the following dataset

Column 1 is a non-unique list of reference IDs Column 2 contains a date stamp every time an interview is completed Column 3 contains a date stamp every time an offer is made

enter image description here

For each ID, I want to count every time there has been an interview, and every time there has been an offer.

In Excel this would be a simple COUNTIF formula

Solution

  • Paste the code below into the Advanced Editor. Change the table name in the Source line to your actual table name in your workbook.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Request ID", type text}, {"Candidate - Interview Completed Date", type date}, {"Candidate - Offer Date", type date}}, "en-150"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Request ID"}, {
        {"Count of Interviews", each List.Count(List.RemoveNulls([#"Candidate - Interview Completed Date"])),Int64.Type},
        {"Count of Offers", each List.Count(List.RemoveNulls([#"Candidate - Offer Date"])), Int64.Type}
        })
in
    #"Grouped Rows"

    enter image description here