如何在Power Query中基于多IF语句实现AD用户Lookup值匹配

本地AD提取表（Extract AD on Prem） #

Email                       NHA_Derived_Responsible_Manager  Fullname    is_zz_em_  Purpose
zz_em_john.smith@mail.com   null                             John Smith  Yes        Business Continuity
zz_em_jane.doe@mail.com     null                             Jane Doe    Yes        Meeting Room
johndoeadmin@mail.com       null                             John Doe    No         Non-Privileged Support Account
mark.obrien@mail.com        obriem01                         Mark Obrien No         Mailbox

Azure AD提取表（Extract AD on Azure） #

原DAX实现代码 #

ResponsibleManagerUsername = LOWER(
    COALESCE(
        IF(
            [Purpose] = "Privileged Support Account" || 
            [Purpose] = "Non-Privileged Support Account", 
            LOOKUPVALUE(
                'Extract AD on Azure'[onPremisesSamAccountName], 
                'Extract AD on Azure'[FromDistinguished], 
                [fullname]
            ),
            IF(
                [is_zz_em_] = "Yes", 
                LOOKUPVALUE(
                    'Extract AD on Azure'[onPremisesSamAccountName], 
                    'Extract AD on Azure'[mail], 
                    SUBSTITUTE([email], "zz_em_", "")
                ), 
                BLANK()
            )
        ),
        IF(
            [Purpose] = "Privileged Support Account" || 
            [Purpose] = "Non-Privileged Support Account", 
            LOOKUPVALUE(
                'Extract AD on Azure'[onPremisesSamAccountName], 
                'Extract AD on Azure'[displayName], 
                [fullname]
            ),
            BLANK()
        ),
        'Extract AD on Prem - NHA Insight'[NHA_Derived_Responsible_Manager]
    )
)

核心匹配逻辑 #

• 当is_zz_em_为Yes时，移除邮箱前缀zz_em_后，在Azure表中匹配mail字段，获取对应的OnPremisesSamAccountName；
• 当Purpose为Privileged Support Account或Non-Privileged Support Account时，先用Fullname匹配Azure表的fromDistinguished字段，匹配失败则再匹配displayName字段，获取对应OnPremisesSamAccountName；
• 若上述匹配均失败，保留原NHA_Derived_Responsible_Manager列的已有值。

Power Query实现方案 #

1. 确保两个表（Extract AD on Prem和Extract AD on Azure）已加载到Power Query编辑器中。
2. 选中Extract AD on Prem表，点击添加列 → 自定义列，输入以下M语言代码：

let
    // 获取当前行的字段值
    currentFullname = [Fullname],
    currentEmail = [Email],
    currentIsZZ = [is_zz_em_],
    currentPurpose = [Purpose],
    originalValue = [NHA_Derived_Responsible_Manager],
    
    // 处理支持账户的匹配逻辑：先匹配fromDistinguished，再匹配displayName
    supportAccountMatch = if List.Contains({"Privileged Support Account", "Non-Privileged Support Account"}, currentPurpose) then
        let
            match1 = Table.SelectRows(#"Extract AD on Azure", each [fromDistinguished] = currentFullname)[OnPremisesSamAccountName],
            match2 = if List.IsEmpty(match1) then Table.SelectRows(#"Extract AD on Azure", each [displayName] = currentFullname)[OnPremisesSamAccountName] else match1
        in
            if List.IsEmpty(match2) then null else Text.Lower(List.First(match2))
    else null,
    
    // 处理zz_em前缀邮箱的匹配逻辑：移除前缀后匹配mail字段
    zzEmMatch = if currentIsZZ = "Yes" then
        let
            cleanedEmail = Text.Replace(currentEmail, "zz_em_", ""),
            match = Table.SelectRows(#"Extract AD on Azure", each [mail] = cleanedEmail)[OnPremisesSamAccountName]
        in
            if List.IsEmpty(match) then null else Text.Lower(List.First(match))
    else null,
    
    // 按优先级取值：优先zz_em匹配结果，再取支持账户匹配结果，最后保留原始值
    finalValue = List.First(List.RemoveNulls({zzEmMatch, supportAccountMatch, originalValue}))
in
    finalValue

3. 将新添加的自定义列重命名为NHA_Derived_Responsible_Manager，覆盖原列（或保留原列后替换）。
4. 点击关闭并上载，完成逻辑实现。

代码说明 #

• 先提取当前行的所有需用字段值，简化后续逻辑调用；
• supportAccountMatch实现支持账户的两次匹配，严格遵循原DAX的匹配优先级；
• zzEmMatch完成邮箱前缀清理与匹配，结果转为小写和原DAX逻辑保持一致；
• finalValue通过List.RemoveNulls和List.First实现类似DAX中COALESCE的优先级取值逻辑。

内容的提问来源于stack exchange，提问作者01nowicj