Lookup values based on multiple if statements in Power Query

Anonymous , 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:

Hi Anonymous ,

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:

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