Export Group Membership From Active Directory Using Power Query

If you need to export a list of staff from an Active Directory Group, follow these steps.  Allows you to gather all the names & usernames of staff in a particular group and save the information into Excel.

Step By Step How To:
  • Open up Excel > Power Query Tab
  • Click From Other Sources > From Active Directory:
  • (In Excel 2016 this is found under: Data Tab > New Query > From Other Sources > From Active Directory)
screenshots from Excel 2016 on left, Excel 2010 w/ Power Query Add-on right
  • A popup will ask you to enter your domain name.  It should already be populated with the correct information but if not, input your companies domain name & Click OK:

  • In the Navigator that opens up, expand your domain tree and double click group:


  • You should now see a list of all groups in Active Directory in the Power Query Editor:

  • In order to find & filter for a specific Group, Click the Down-Arrow for the 'distinguishedName' column > Text Filters > Contains...

  • Type the name of your Group & hit OK (Text box is case sensitive!):
Here I am looking for users of the group 'Enable_EB_Screensaver'.

  • Now we want to list all of the staff that are members of the group.  To do this, Expand the group column and unselect everything except the 'member' column:

  • Now expand the group.member column:

You should now have a row for every member inside the group:

Right-Click the group.member column and select 'Unpivot Columns'

  • You should now see a 'Value' column at the end of your spreadsheet.  Click the expand button and select the following columns: displayName, sAMAccountName
  • Now you should have a nice list of all staff that are members of your Active Directory Group!  Click 'Close & Load' to load the data into Excel!


Quick Method How To:

If you're already a bit of a wiz with Power Query, simply copy this code into the Advanced Editor and replace anything highlighted in RED with your own organisational information.

let
    Source = ActiveDirectory.Domains("[DomainName]"),
    [DomainName] = Source{[Domain="[DomainName]"]}[#"Object Categories"],
    group1 = [DomainName]{[Category="group"]}[Objects],
    #"Filtered Rows" = Table.SelectRows(group1, each Text.Contains([distinguishedName], "[ActiveDirectoryGroupName]")),
    #"Expanded group" = Table.ExpandRecordColumn(#"Filtered Rows", "group", {"member"}, {"group.member"}),
    #"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded group.member", {"displayName", "top", "posixGroup", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes", "mailRecipient", "securityPrincipal", "distinguishedName"}, "Attribute", "Value"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"displayName", "sAMAccountName"}, {"Value.displayName", "Value.sAMAccountName"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value",{"displayName", "top", "posixGroup", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes", "mailRecipient", "securityPrincipal", "distinguishedName", "Attribute"})
in
    #"Removed Columns"


Notes

Feedback
How did you go?  Loving it?  If so, check out my other posts on Power Query:

Comments

  1. Thanks very much mate. Just what I was looking for. Clear, concise and easy to follow.

    ReplyDelete
  2. hi - is it possible to setup the query to read the domain and the group name from two cells that contain the domain and group name information in tab1 and in tab2 to have the group be expanded with its members?

    ReplyDelete
    Replies
    1. Hi Jose, It sounds like what you're asking is to have two separate spreadsheets and show group name in one spreadsheet tab, and group members in the second spreadsheet tab.

      Should be possible but may need two separate Power Queries.

      Alternatively, you could keep the distinguished name column and possibly create a pivot table to show list of users based on which group you have selected?

      Delete
  3. What if we have a group with nested DL's, can we query for indirect members?

    ReplyDelete
    Replies
    1. Hi, I'm currently struggling with this problem. Were you able to get an answer?

      Thanks,
      Michael

      Delete
  4. Thank you for this very clear explanation.
    The problem is that when your AD is huge (hundreds of thousands of groups), it seems a waste to have to first fetch all the distinguishedName in order to be able to filter only the one(s) you need. It is actually taking a long long time to do this. Would there be any way to fetch directly the distinguishedName(s) that are needed instead of the whole list?

    ReplyDelete
  5. Thanks Brett I used this today :)

    ReplyDelete
  6. Great thank you for this, very useful

    ReplyDelete
  7. sweet thanks, just what I was looking for.

    ReplyDelete
  8. This is a great article.

    Is there a way to connect the member information? Mainly, I'm trying to get the status of the member (disabled).

    This is so much more user friendly than PS but at the same time more difficult.

    ReplyDelete
  9. Is there a way to pull the date the user was added to the group? I couldn't see a field at first glance but hoped there was at least a chance.

    ReplyDelete
  10. Many thanks for the post!!!
    I have follow your recommendation but unfortunately cannot see the group I am searching... The group I am looking for is created by me in Team App (but also SharePoint link is available)
    Is the any idea how to find such groups created by members?
    Thanks in advance!

    ReplyDelete
  11. This really helped me loads today. Thank you very much!

    ReplyDelete
  12. Thank you So much!! I was searching for these steps from two days!!!

    ReplyDelete

Post a Comment

Popular posts from this blog

SharePoint - Field type [FieldName] is not installed properly. Go to the list settings page to delete this field.

Office 365 Groups - Quickly find the GUID of a O365 Group