COUNTIFS in M language over two tables

Question

My objective is to determine whether certain users have been set up correctly in the finance system.

To determine this, I have a table of users showing their name, team, specific finance role and their permissions, similar to this:
Table name: USERS

| User       | Team | ApprovalRole | UserGroup        |
| ----       | ---- | ------------ | ---------        |
| User One   | ABC  | <75K         | APPROVER         |
| User Two   | ABC  | <1M          | APPROVER         |
| User Three | ABC  | <500K        | BASIC            |
| User Four  | TRD  | POA-1,000    | PO APPROVER      |
| User Five  | TRD  | POA-50,000   | PO APPROVER      |
| User Six   | ZBQ  | INV_APP      | INVOICE APPROVER |
| User Seven | ZBQ  | 0            | BASIC            |
| User Eight | YHW  | 0            | BASIC            |
| User Nine  | YHW  | CEO          | BASIC            |
| User Ten   | YHW  | INVAPP       | INVOICE APPROVER |

I also have a lookup-table which lists the "correct" set-up of the roles which I'm monitoring. Below is a sample table showing the structure:
Table name: LOOKUPS

| RoleName         | Team | ApprovalRole | UserGroup        |
| --------         | ---- | ------------ | ---------        |
| PO Approver      | ABC  | <            | Approver         |
| PO Approver      | TRD  | POA-         | PO Approver      |
| PO Approver      | ZBQ  | CEO          | Approver         |
| Invoice Approver | ZBQ  | INV_APP      | Invoice Approver |
| Invoice Approver | YHW  | INVAPP       | Invoice Approver |

My end-goal is to have a column added to USERS table which identifies whether these types of users are set-up correctly.

APPROACH 1
My preferred approach would be like a COUNTIFS in Excel.
SUM(COUNTIFS(LOOKUPS[Team],[@Team],LOOKUPS[ApprovalRole],[@ApprovalRole]),COUNTIFS(LOOKUPS[Team],[@Team],LOOKUPS[UserGroup],[@UserGroup])).
This will give a score of 0, 1 or 2:

• 0's I can ignore - they aren't connected to this activity at all;
• 1's are errors. They either have the ApprovalRole OR they have the UserGroup, but not both. These need to be fixed;
• 2's are passes - everything is fine so nothing to worry about.

Q1: If I go down this route, can someone provide some help on how best to code these formulae in M language? I've tried to follow the post from JayKilleen but this only works for a single comparision; I need to compare two fields in both parts of my SUM formula.
Q2: I understand M uses case-sensitive coding. I can create a column for LOOKUPS[UserGroup] in capitals. But is there are way to add this into COUNTIFS-equivalent formula so it's done in one step (COUNTIFS reads the field as capitals), rather than two (step one, convert to capitals, step two, run the COUNTIFS)?
Q3: How would I add a wildcard search so User One, User Two, User Four and User Five are correctly identified as a PO Approver?

APPROACH 2
I could merge the two tables together. To do this, I'd need a Lookup-Helper column on each table comprising Team-UserGroup-ApprovalRole.
I don't think this approach would wholly work; this report is going to act as a quality assurance check so needs to highlight errors in the processing. In light of this, User Three and User Nine would not be identified as having a valid UserGroup for the activity in progress.
Nevertheless:
Q4: How can I add a wildcard so that User One, User Two, User Four and User Five are correctly identified as "PO Approver?"
Q5: Can someone suggest how the two tables could be merged? The helper column on LOOKUPS is = Table.AddColumn(#"Added Custom", "Helper", each [Team]&[CapitalUserGroup]&[ApprovalRole]&"*") which includes the * wildcard, but when I try to merge this with USERS, zero lines match. If I extend this to fuzzy matches, User Six and User Ten returns two matches: both of the Invoice Approvers. This also fails to identify User Three and User Nine as possibles.

I'm very new to M and don't know what it can do. Much of my research online has returned results in DAX which isn't helpful.

I'd be extremely grateful for any steer to achieving the goal.

Answer 1

If I understand your logic, I think this will do the trick.

• You are checking to ensure that the Team to which a user is assigned is associated with valid approval and usergroup
  • If I have misunderstood, and the Team is irrelevant, let me know
• Join the two tables based on Team
• Ensure that for each, the approval and usergroup in the USERS table starts with the same characters as in the relevant LOOKUPS table.

Please read the comments and examine the applied steps to understand:

let
    Source = Excel.CurrentWorkbook(){[Name="USERS"]}[Content],
    users = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_,type text})),

//read Lookups table
    Source2 = Excel.CurrentWorkbook(){[Name="LOOKUPS"]}[Content],
    lookups = Table.TransformColumnTypes(Source2,
        List.Transform(Table.ColumnNames(Source2), each {_, type text})),

//join the two tables based on team
    joined=Table.NestedJoin(users,"Team",lookups,"Team", "joined",JoinKind.LeftOuter),

//check for validities
    validApprovalRole = Table.AddColumn(joined, "Valid Approval Role", each 
        let 
            validRoles = [joined][ApprovalRole],
            foundValidRole = List.Accumulate(
                validRoles,false,(state,current)=> 
                    if state = true then true else Text.StartsWith([ApprovalRole],current,Comparer.OrdinalIgnoreCase))
        in 
            foundValidRole, type logical),

    validUserGroup = Table.AddColumn(validApprovalRole, "Valid UserGroup", each 
        let 
            validGroups = [joined][UserGroup],
            foundValidGroup = List.Accumulate(
                validGroups,false,(state,current)=> 
                    if state = true then true else Text.StartsWith([UserGroup],current,Comparer.OrdinalIgnoreCase))
        in 
            foundValidGroup, type logical),
    #"Removed Columns" = Table.RemoveColumns(validUserGroup,{"joined"})
in
    #"Removed Columns"