Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Lookup values based on multiple if statements in Power Query

Hello,    I have two tables of Active Directory users:    1. Extract AD on Prem email NHA_Derived_Responsible_Manager fullname is_zz_em_ Purpose [email protected] null John S...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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:

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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