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. Hoggy17 October 2016 at 17:59

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

    ReplyDelete
  2. Unknown11 February 2017 at 12:22

    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. The Baretta10 April 2017 at 15:42

      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. timetrex4 March 2017 at 01:22

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

    ReplyDelete
    Replies
    1. Unknown30 October 2018 at 02:59

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

      Thanks,
      Michael

      Delete
  4. maphew12 September 2017 at 05:44

    THANK YOU!

    ReplyDelete
  5. NJBB18 January 2018 at 02:16

    excellent

    ReplyDelete
  6. Unknown9 February 2018 at 01:57

    Thank you ! very nice

    ReplyDelete
  7. Unknown13 March 2018 at 17:42

    Great post! Thank you!

    ReplyDelete
  8. Unknown28 June 2018 at 11:39

    Thanks Brett I used this today :)

    ReplyDelete
  9. Unknown22 August 2018 at 03:50

    Great thank you for this, very useful

    ReplyDelete
  10. Robbie24 October 2018 at 10:23

    sweet thanks, just what I was looking for.

    ReplyDelete
  11. Steve28 March 2019 at 04:13

    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
  12. Unknown7 May 2020 at 06:40

    THANK YOU FOR THIS!

    ReplyDelete
  13. MattM18 June 2020 at 09:42

    Dude, yes. Thanks!

    ReplyDelete
  14. Unknown23 September 2020 at 03:36

    Great walkthrough! Thank you

    ReplyDelete
  15. C. Shumate13 November 2020 at 04:13

    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
  16. Unknown23 December 2020 at 00:55

    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
  17. Unknown30 December 2020 at 03:21

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

    ReplyDelete
  18. Ashwin18 January 2021 at 13:59

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

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Copy/Duplicate a Table and Columns in a Dataverse Environment

-
Image
What If you need to do either of these things, then this article is for you: Create a copy of a table in the same environment as the original table Create a copy of a table but want a different prefix against custom column names Why In most circumstances, if you need to duplicate a dataverse table, I would suggest to bundle it inside a solution, and migrate the solution to another environment.   However, in the scenario where you need the duplicate table to be inside the same environment, here's a quick method that requires no manual creation of columns! How Short answer : Install  XRMToolBox , inside XRMToolBox, install and utilise the Clone Field Definitions Plugin  from the Tool Library. Pre-Tasks In order to clone a table with a new PREFIX value for the columns you first need to do the following: Create a new PUBLISHER with the new prefix you would like Create a new TABLE where you will be cloning all the fields. Tasks Download & Install  XRMToolBox On t...
Read more

Microsoft Flow - Apply To Each Limitation (5000 items)

-
Image
What Had a situation where I was pulling back 70,000 items in an array from a REST API call.  I wanted to create a SharePoint item for each of the items in an array, however the 'Apply To Each' action kept giving me the following error: ActionFailed . An action failed. No dependent actions succeeded. Why This was due to a Microsoft Flow limitation. When you are using the Free Version of MS Flow you are limited to 5,000 items in a loop .  Explained here:  https://docs.microsoft.com/en-us/flow/limits-and-config Workaround I tried my best to come up with a workaround, tried splitting up the array into batches of 5,000.  All to no avail.  in the end, gave up, and saved the array of items to a CSV and saved the CSV in a SharePoint Library instead. body('Parse_JSON')?['applist']?['apps'] The only other alternative I could come up with is to grab the 'first 4999 items' from the array, which does work with an expression like this: ...
Read more