两个表上M语言的COUNTIFS

Question

我的目标是确定某些用户是否在金融系统中被正确设置。

为了确定这一点，我有一个用户表，显示他们的名称、团队、特定的财务角色和他们的权限，类似于这样：

表名：用户

| 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 |

我还有一个查找表，它列出了我正在监视的角色的“正确”设置。下面是一个显示结构的示例表：

表名：查找

| 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 |

我的最终目标是在用户表中添加一列，以标识这些类型的用户是否正确设置。

方法1

我喜欢的方法就像Excel中的COUNTIFS。

SUM(COUNTIFS(LOOKUPS[Team],[@Team],LOOKUPS[ApprovalRole],[@ApprovalRole]),COUNTIFS(LOOKUPS[Team],[@Team],LOOKUPS[UserGroup],[@UserGroup]))。

这将给出0，1或2的分数：

• 我可以忽略的是--它们与这个活动一点关系都没有；
• 我是错误的。他们要么拥有ApprovalRole，要么拥有UserGroup，但不是两者兼有。这些需要加以修正；
• 一切都很好，所以没什么好担心的。

Q1：如果我沿着这条路线走下去，有人能提供一些关于如何最好地用M语言编写这些公式的帮助吗？我试图跟随JayKilleen的文章，但这只适用于一个比较；我需要比较我的和公式的两个部分中的两个字段。

Q2：我知道M使用区分大小写的编码。我可以为LOOKUPSUserGroup创建一个大写的列。但是，是否有办法将其添加到COUNTIFS-等效公式中，以便在一步内完成(COUNTIFS将字段读取为大写)，而不是第二步(第一步，转换为大写，步骤二，运行COUNTIFS)？

Q3：我如何添加通配符搜索，以便正确识别用户1、用户2、用户4和用户5为PO？

方法2

我可以把这两张桌子合并在一起。要做到这一点，我需要在每个表上都有一个Lookup-Helper列，该列由Team-UserGroup-ApprovalRole组成。

我不认为这种方法会完全奏效；这份报告将作为质量保证检查，因此需要强调处理过程中的错误。有鉴于此，用户三和用户九将不会被识别为对正在进行的活动具有有效的UserGroup。

然而：

Q4：如何添加通配符，使用户一、用户二、用户四和用户五被正确识别为"PO“？

Q5：有人能建议如何合并这两个表吗？查找中的助手列是= Table.AddColumn(#"Added Custom", "Helper", each [Team]&[CapitalUserGroup]&[ApprovalRole]&"*")，它包含*通配符，但是当我试图与用户合并时，零行匹配。如果将此扩展到模糊匹配，用户六和用户十将返回两个匹配:两个发票审批者。这也无法确定用户三和用户九是可能的。

我对M很陌生，不知道它能做什么。我在网上的许多研究已经在DAX中返回了结果，这是没有帮助的。

我非常感谢为实现这一目标而采取的任何行动。

Answer 1

如果我能理解你的逻辑，我想这会有效果的。

• 您正在检查以确保分配给用户的团队与有效的批准和用户组相关联。
  • 如果我误解了，而且团队是无关紧要的，请告诉我

• 加入--基于团队的两个表
• 确保“用户”表中的“批准和用户组”以与相关查找表中相同的字符开头。

请阅读评论意见，并仔细研究所采用的步骤，以了解：

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"

​

​