Lookup values based on multiple if statements in Power Query

[nowicj01]

Hello, 

I have two tables of Active Directory users: 

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

2. Extract AD on Azure

mail	OnPremisesSamAccountName	displayName	fromDistinguished
john_smith@mail.com	smithj01	John Smith	John Smith
john.doe@mail.com	doej01	Jonnie Doe	John Doe
jane_doe@mail.com	doej01	Jane Doe	Jane Doe

I've been trying to find the NHA_Derived_Responsible_Manager, and I succeded in DAX, however the requirements have changed and now I have to do it in PowerQuery: 

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

This snippet shows the logic that I need to have, three LOOKUPVALUEs based on three IF statements. 

I need to change the existing Extract AD on Prem[NHA_Derived_Responsible_Manager] to include the OnPremisesSamAccountName. I've tried a lot of different code to no avail and this is my most recent attempt: 

= Table.AddColumn(#"Trimmed Text1", "AzureUsername", each if [is_zz_em_] = "yes" then
    let
        EmailWithoutPrefix = Text.Remove([email], "zz_em_"),
        LookupRow = Table.SelectRows(#"Extract AD on Azure", each [mail] = EmailWithoutPrefix)
    in
        if Table.IsEmpty(LookupRow) then null else LookupRow{0}[onPremisesSamAccountName]
else
    null)

I only tried to add a column with the lookup, but even that isn't working. 

Any help much appreciated!

[Poojara_D12]

Hi @nowicj01 ,

To achieve this in Power Query with your described logic, we can break it down into a series of conditional if...then...else checks to determine the value for NHA_Derived_Responsible_Manager based on your requirements.

Here's how to accomplish it:

1. Extract the email without the zz_em_ prefix for rows where [is_zz_em_] is "Yes".
2. Look up the onPremisesSamAccountName in the Azure table using three different fields: mail, fromDistinguished, and displayName, based on the Purpose and is_zz_em_ conditions.

Below is the Power Query M code that performs these steps in one calculated column:

powerquery

// Start by adding a new column
= Table.AddColumn(#"Trimmed Text1", "AzureUsername", each 
    let
        // Condition 1: Check if the Purpose is "Privileged Support Account" or "Non-Privileged Support Account"
        Condition1 = 
            if [Purpose] = "Privileged Support Account" or [Purpose] = "Non-Privileged Support Account" then
                let
                    LookupRow = Table.SelectRows(#"Extract AD on Azure", each [fromDistinguished] = [fullname])
                in
                    if not Table.IsEmpty(LookupRow) then LookupRow{0}[onPremisesSamAccountName] else null
            else null,

        // Condition 2: Check if is_zz_em_ is "Yes" and do email-based lookup
        Condition2 = 
            if [is_zz_em_] = "Yes" then
                let
                    EmailWithoutPrefix = Text.Replace([email], "zz_em_", ""),
                    LookupRow = Table.SelectRows(#"Extract AD on Azure", each [mail] = EmailWithoutPrefix)
                in
                    if not Table.IsEmpty(LookupRow) then LookupRow{0}[onPremisesSamAccountName] else null
            else null,

        // Condition 3: Fallback if Condition 1 and 2 are blank, using fullname and displayName lookup for certain purposes
        Condition3 =
            if (Condition1 = null and Condition2 = null) and ([Purpose] = "Privileged Support Account" or [Purpose] = "Non-Privileged Support Account") then
                let
                    LookupRow = Table.SelectRows(#"Extract AD on Azure", each [displayName] = [fullname])
                in
                    if not Table.IsEmpty(LookupRow) then LookupRow{0}[onPremisesSamAccountName] else null
            else null
    in
        // Final logic to return the first non-null condition or default value
        if Condition1 <> null then Condition1
        else if Condition2 <> null then Condition2
        else if Condition3 <> null then Condition3
        else [NHA_Derived_Responsible_Manager]  // Fallback to the original value if all conditions are null
)

• Ensure that #"Extract AD on Azure" is correctly referenced and available in the Power Query context.
• Adjust the field names if they differ in your actual data model.

This Power Query formula should satisfy your requirements and replicate the conditional lookup logic you implemented in DAX. Let me know if you have questions about modifying or troubleshooting it!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

[nowicj01]

Hi, 

Thank you so much for looking into this, I really appreciate it. I have tested this solution but it seems to get the "zz_em_" bit right but for the rest it defaults to the NHA_Derived_Responsible_Manager column for some reason. 

[Poojara_D12]

Hi, @nowicj01 

STRANGE!!! It might work as expected, don't know why you're not able to see it for the rest.

[v-bofeng-msft · Accepted solution]

Hi @nowicj01 ,

Please try this m code:

let
    // Extract AD on Prem table
    ExtractADOnPrem = Table.FromRows({
        {"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"}
    }, {"email", "NHA_Derived_Responsible_Manager", "fullname", "is_zz_em_", "Purpose"}),

    // Extract AD on Azure table
    ExtractADOnAzure = Table.FromRows({
        {"john_smith@mail.com", "smithj01", "John Smith", "John Smith"},
        {"john.doe@mail.com", "doej01", "Jonnie Doe", "John Doe"},
        {"jane_doe@mail.com", "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

[bhanu_gautam]

@nowicj01 , You can try below steps

Load both tables into Power Query: Ensure that both "Extract AD on Prem" and "Extract AD on Azure" tables are loaded into Power Query.

Add a custom column: Use the Table.AddColumn function to add a new column AzureUsername with the required logic.

Here is the complete Power Query M code to achieve this:

let
// Load the "Extract AD on Prem" table
Source = ...,
#"Trimmed Text1" = Table.TransformColumns(Source,{{"email", Text.Trim, type text}}),

// Load the "Extract AD on Azure" table
AzureSource = ...,

// Add the "AzureUsername" column

Explanation:

[nowicj01]

So my goal is not the AzureUsername, just to fill in some blanks in NHA_Derived_Responsible_Manager, following the same logic as the DAX