Lookup values based on multiple if statements in Power Query

Hi Anonymous ,

Please try this m code:

let
    // Extract AD on Prem table
    ExtractADOnPrem = Table.FromRows({
        {"[email protected]", null, "John Smith", "Yes", "Business Continuity"},
        {"[email protected]", null, "Jane Doe", "Yes", "Meeting Room"},
        {"[email protected]", null, "John Doe", "No", "Non-Privileged Support Account"},
        {"[email protected]", "obriem01", "Mark Obrien", "No", "Mailbox"}
    }, {"email", "NHA_Derived_Responsible_Manager", "fullname", "is_zz_em_", "Purpose"}),

    // Extract AD on Azure table
    ExtractADOnAzure = Table.FromRows({
        {"[email protected]", "smithj01", "John Smith", "John Smith"},
        {"[email protected]", "doej01", "Jonnie Doe", "John Doe"},
        {"[email protected]", "doej01", "Jane Doe", "Jane Doe"}
    }, {"mail", "OnPremisesSamAccountName", "displayName", "fromDistinguished"}),

    // Format email addresses
    FormatExtractADOnPrem = Table.AddColumn(ExtractADOnPrem, "formatted_email", each Text.Replace([email], "zz_em_", ""), type text),

    // Merge tables based on fullname and formatted email
    MergedTable1 = Table.NestedJoin(FormatExtractADOnPrem, "fullname", ExtractADOnAzure, "fromDistinguished", "AzureByFullname", JoinKind.LeftOuter),
    MergedTable2 = Table.NestedJoin(MergedTable1, "formatted_email", ExtractADOnAzure, "mail", "AzureByEmail", JoinKind.LeftOuter),

    // Add custom column: ResponsibleManagerUsername
    AddResponsibleManager = Table.AddColumn(MergedTable2, "ResponsibleManagerUsername", each 
        Text.Lower(
            if [Purpose] = "Privileged Support Account" or [Purpose] = "Non-Privileged Support Account" then
                if [AzureByFullname] <> null and Table.RowCount([AzureByFullname]) > 0 then 
                    [AzureByFullname]{0}[OnPremisesSamAccountName]
                else 
                    null
            else if [is_zz_em_] = "Yes" then
                if [AzureByEmail] <> null and Table.RowCount([AzureByEmail]) > 0 then 
                    [AzureByEmail]{0}[OnPremisesSamAccountName]
                else 
                    null
            else 
                [NHA_Derived_Responsible_Manager]
        )
    ),

    // Remove unnecessary columns
    RemoveExtraColumns = Table.RemoveColumns(AddResponsibleManager, {"formatted_email", "AzureByFullname", "AzureByEmail"})
in
    RemoveExtraColumns

Best Regards,

Bof