locked
Table Context Confusion RRS feed

  • Question

  • Hi guys

    The following script attempts to retrieve Active Directory information for each email address in an input table:

    let
        Source = Excel.CurrentWorkbook(){[Name="tbl_Input"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email Addresses", type text}, {"Domain", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "UserData", each fxEmailLookup( [Domain] )),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.SelectRows([UserData], each [mail] = [Email Addresses] ))
    in
        #"Added Custom1"

    Unfortunately I'm experiencing this error:

    

    This happens because "each [email] = [Email Addresses]" is executed within the context of the [UserData] table (where [Email Addresses] doesn't exist).  I would like it to filter [email] by #"Added Custom"[Email Addresses]. 

    I've tried "each [mail] = #"Added Custom"[Email Addresses]" but that returns a blank table.

    Suggestions?

    Thanks,
    Simon

    Friday, May 6, 2016 1:39 PM

Answers

  • Sorry - another "this" missing:

        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", (this) => Table.SelectRows(this[UserData], each [mail] = this[Email Addresses] ))


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Simon Nuss Friday, May 6, 2016 6:59 PM
    Friday, May 6, 2016 3:42 PM

All replies

  • You have nested environment here. Try this:

        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", (this) => Table.SelectRows([UserData], each [mail] = this[Email Addresses] ))

    Beware of syntax sugar: https://msdn.microsoft.com/en-us/library/mt185361.aspx


    Imke Feldmann TheBIccountant.com


    Friday, May 6, 2016 2:32 PM
  • This for the quick response Imke!  Unfortunately I received the following error:

    Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

    Friday, May 6, 2016 3:11 PM
  • Sorry - another "this" missing:

        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", (this) => Table.SelectRows(this[UserData], each [mail] = this[Email Addresses] ))


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Simon Nuss Friday, May 6, 2016 6:59 PM
    Friday, May 6, 2016 3:42 PM
  • Thanks Imke!  Works like a charm.
    Friday, May 6, 2016 6:59 PM