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:

EmployeeCounterDays
A11
A25
B16
B21
C11

 

It should output for example:

Number of employees ==> 3

Number of different counters ==> 2

Sum of days == >14

 

So we use ye olde method of 1/COUNTIFS() and then sum on that column. I know, not ideal, but it works.

 

I found a method in M language on this forum and StackOverflow, based on this code:

 

 

 

List.Count(
    Table.SelectRows(
       #"PREVIOUS_STEP",
(C) => [Matricule] = C[VALUE_1] and [VALUE_2] = (C)[VALUE_2]
    )[VALUE_1]
)

 

 

 

 

Problem is, it is very, very slow.

 

Isn't there any way to do it faster and in a simpler way in Power Query? We do not want to use DAX for this, as we need the data in Excel.

 

Thanks in advance for any help.

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

8 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Spigaw ,

     

    The counter can be done quickly using a nested index, but what was it you wanted done with the days?

    Can you give a small example of your original data, and an example of the expected output please?

     

    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
    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      *EDIT* Ignore this post, check out my reply to this post below - much simpler and maintains query folding

      ------------------------------------------------------------------------------------------

       

      Cool, thanks for that.

      Try this out:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsEzBLCcgywzOgsg6Q1ixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Days = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Days", Int64.Type}}),
    groupEmployeeData = Table.Group(chgTypes, {"Employee"}, {{"data", each _, type table [Employee=nullable text, Counter=nullable number, Days=nullable number]}}),
    addNestedCounter = Table.TransformColumns(groupEmployeeData, {"data", each Table.AddIndexColumn(_, "Counter", 1, 1)}),
    expandDataCol = Table.ExpandTableColumn(addNestedCounter, "data", {"Days", "Counter"}, {"Days", "Counter"}),
    addGroupMe = Table.AddColumn(expandDataCol, "GroupMe", each "X"),
    groupRows = Table.Group(addGroupMe, {"GroupMe"}, {{"NoEmployee", each List.Count(List.Distinct(_[Employee])), Int64.Type}, {"NoCounter", each List.Max([Counter]), type number}, {"SumOfDays", each List.Sum([Days]), type number}})
in
    groupRows

       

       

      Summary:

      groupEmployeeData = Nest a table of data for each employee

      addNestedCounter = Add an Index column to each employee table for the counter

      expandDataCol = Reinstate nested tables/columns to main table

      addGroupMe = Add a single-value column on which to group the whole table

      groupRows = Group no single-value column using MAX of [Counter], SUM of [Days], and a custom group using List.Count(List.Distinct(... on [Employee]

       

      It *should* be faster than a custom function, but too many variables to know for sure.

       

      Pete

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Ignore post above, you can do this with just a double-group, which will also maintain query folding:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsEzBLCcgywzOgsg6Q1ixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Days = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Days", Int64.Type}}),
    groupEmployeeData = Table.Group(chgTypes, {"Employee"}, {{"NoCounter", each Table.RowCount(_), Int64.Type}, {"SumOfDays", each List.Sum([Days]), type nullable number}}),
    addGroupMe = Table.AddColumn(groupEmployeeData, "GroupMe", each "X"),
    groupTable = Table.Group(addGroupMe, {"GroupMe"}, {{"NoEmployee", each Table.RowCount(_), Int64.Type}, {"NoCounter", each List.Max([NoCounter]), type number}, {"SumOfDays", each List.Sum([SumOfDays]), type nullable number}})
in
    groupTable

         

        Pete