none
Basic AD query RRS feed

  • Question

  • Hi,

    I'm looking into using Power Query so that I can get up-to-date Active Directory information about our users. I want to be able to extract basic data like names, email addresses, department etc. When I query the user table in Power Query it returns the display names of all the users but no other useful columns (it shows things like securityPrinciple and posixAccount though). Is there some simple way I can use Power Query to combine data from multiple AD tables to provide a cohesive view of all our user data? I don't know the names of all the fields I want, so I'd rather just extract all the data and figure it out from there.

    Thanks in advance for anyone that can offer any help.

    Friday, May 9, 2014 9:24 AM

Answers

  • When you connect to the user table, as you noted, there's a column of displayName and then a bunch of other columns. Are you familiar with the expand button? Most of the other columns in this table are links to other related tables so those columns have a little button to the right of the column name that looks like two arrows pointed left and right. Click that and then it will let you choose which of the embedded/related columns that you want to add to your table. I expect that the user, organizationalPerson, and person columns probably contain most of the data you want. But if you want to blow out every single column and then go from there (not good for performance but maybe easier to dig through) then just go to each column and click those expand buttons. You should end up with a huge table of data for each user.

    This is one of my favorite features in Power Query. It's a great way to dig through a data schema that you're not quite familiar with and pull in related information without worrying about joins and merges.

    Friday, May 9, 2014 3:45 PM
    Moderator

All replies

  • When you connect to the user table, as you noted, there's a column of displayName and then a bunch of other columns. Are you familiar with the expand button? Most of the other columns in this table are links to other related tables so those columns have a little button to the right of the column name that looks like two arrows pointed left and right. Click that and then it will let you choose which of the embedded/related columns that you want to add to your table. I expect that the user, organizationalPerson, and person columns probably contain most of the data you want. But if you want to blow out every single column and then go from there (not good for performance but maybe easier to dig through) then just go to each column and click those expand buttons. You should end up with a huge table of data for each user.

    This is one of my favorite features in Power Query. It's a great way to dig through a data schema that you're not quite familiar with and pull in related information without worrying about joins and merges.

    Friday, May 9, 2014 3:45 PM
    Moderator
  • Hi Ben,

    Thanks for helping out on this. I can't believe I didn't notice that :-/  I'm able to retrieve the user data now and all is good! :-)

    Many thanks

    Monday, May 12, 2014 12:33 PM