none
PowerQuery & Active Directory Source - How to pull manager displayName? RRS feed

  • Question

  • I was able to expand the user column and get the manager "table". But not certain how to expand the manager table to get the manager's displayName. Does anyone know how to expand a nested column (if I'm saying that correctly). Current query is below. Any assistance is appreciated. Thank you!

    let
        Source = ActiveDirectory.Domains("domain.com"),
        domain.com = Source{[Domain="domain.com"]}[#"Object Categories"],
        user1 = domain.com{[Category="user"]}[Objects],
        #"Expand user" = Table.ExpandRecordColumn(user1, "user", {"mail", "manager", "userPrincipalName"}, {"user.mail", "user.manager", "user.userPrincipalName"}),
    in
        #"Expand user"


    http://www.blogmynog.com

    Monday, January 26, 2015 10:46 PM

Answers

  • You can just expand the manager record's properties using the expand button on the column:

    This query does what you want also:

    let
        Source = ActiveDirectory.Domains("domain.com"),
        domain.com = Source{[Domain="domain.com"]}[#"Object Categories"],
        user1 = domain.com{[Category="user"]}[Objects],
        #"Expand user" = Table.ExpandRecordColumn(user1, "user", {"mail", "manager", "userPrincipalName"}, {"user.mail", "user.manager", "user.userPrincipalName"}),
        #"Removed Other Columns" = Table.SelectColumns(#"Expand user",{"user.userPrincipalName", "user.manager", "user.mail", "displayName"}),
        #"Expand user.manager" = Table.ExpandRecordColumn(#"Removed Other Columns", "user.manager", {"displayName"}, {"user.manager.displayName"})
    in
        #"Expand user.manager"

    Wednesday, January 28, 2015 12:37 AM
    Moderator

All replies

  • Hi,

    From you description, this issue is related to the PowerQuery, I'll move your thread to PowerQuery forum:

    https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    Forum Support
    ________________________________________
    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please click "tnfsl@microsoft.com"

    Tuesday, January 27, 2015 8:33 AM
  • You can just expand the manager record's properties using the expand button on the column:

    This query does what you want also:

    let
        Source = ActiveDirectory.Domains("domain.com"),
        domain.com = Source{[Domain="domain.com"]}[#"Object Categories"],
        user1 = domain.com{[Category="user"]}[Objects],
        #"Expand user" = Table.ExpandRecordColumn(user1, "user", {"mail", "manager", "userPrincipalName"}, {"user.mail", "user.manager", "user.userPrincipalName"}),
        #"Removed Other Columns" = Table.SelectColumns(#"Expand user",{"user.userPrincipalName", "user.manager", "user.mail", "displayName"}),
        #"Expand user.manager" = Table.ExpandRecordColumn(#"Removed Other Columns", "user.manager", {"displayName"}, {"user.manager.displayName"})
    in
        #"Expand user.manager"

    Wednesday, January 28, 2015 12:37 AM
    Moderator