Forum Discussion

Spigaw's avatar
Spigaw
Helper III
3 years ago
Solved

COUNTIFS in Power Query (M)

Hi there,   We use a lot of COUNTIFS() in Excel, because we have to extract unique occurrences of employees in order to make sure they don't appear multiple times.   For example, in this table: ...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    OK, so I started from scratch just to make sure I was working through the issue correctly. I've attached the working PBIX at the bottom so I won't post each individual bit of code here.

     

    - Your Days table I didn't do anything with directly, but is used later in a crossjoin.

    - Your Events table I did some pre-prep on to get it looking like this:

     

    - Your Employees table I used as the base for the final process, crossjoining the Dates table and merging the prepped Events table, before double-grouping to generate the daily stats.

    Here's the output:

     

    I think you'd got most of the way here already, so it's probably just the final groupings in the Employees table that are relevant. The first grouping just gets you to a single record per employee/date so you're not double-counting. The second grouping uses custom List.Counts to pick out the stats you're after.

     

    Pete

JW_van_Holst's avatar
JW_van_Holst
Resolver IV
3 years ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE41gdCM8IiE3BPCeonBmcZwRX6YzQFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Counter = _t, Days = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Days", Int64.Type}}),
    DistEmployee = List.Count(List.Distinct(ChangedType[Employee])),
    DistCounter = List.Count(List.Distinct(ChangedType[Counter])),
    SumOfDays = List.Sum(ChangedType[Days]),
    Result = Table.FromRecords({[NoEmployee = DistEmployee, NoCounter = DistCounter, SumOfDays = SumOfDays]})

in
   Result