I'm trying to pull back the members of an AD distribution group using Excel's Power Query tool.
Using the Active Directory data source I can query for all users on a domain. Following is the generated formula: = mydomain.mycompany.com{[Category="user"]}[Objects]
I'm hoping to find some way to refine this, either by updating the formula or adding steps, to allow the query to filter for only those users who are a member of a given security group (ideally this would include recursive memberships).
I'm using Power Query downloaded from: http://www.microsoft.com/en-gb/download/details.aspx?id=39379 with Excel 2013.
Thanks in advance.
Update 2021
A colleague recently hit the same issue and we rediscovered this old post... Here's a tweaked version of the accepted answer which he found useful (this has our parameters hardcoded in the query so the example's easily usable; in reality those would be passed in from outside). More info.
let
parmDomainFqdn = "-put the domain's FQDN here-",
parmGroupSAMAccountName = "-put the group's SAMAccountName here-",
Source = ActiveDirectory.Domains(parmDomainFqdn),
selectedDomain = Source{[Domain=parmDomainFqdn]}[Object Categories],
groups = selectedDomain{[Category="group"]}[Objects],
groupSAMAccountNameExpanded = Table.ExpandRecordColumn(groups, "securityPrincipal", {"sAMAccountName"}, {"groupSAMAccountName"}),
ourGroup = Table.SelectRows(groupSAMAccountNameExpanded, each ([groupSAMAccountName] = parmGroupSAMAccountName)),
ourGroupRecordExpanded = Table.ExpandRecordColumn(ourGroup, "group", {"member"}, {"ourGroupMembersList"}),
ourMemberListExpanded = Table.ExpandListColumn(ourGroupRecordExpanded, "ourGroupMembersList"),
ourGroupMembersList = ourMemberListExpanded{0}[ourGroupMembersList],
membersWithFields = Table.ExpandRecordColumn(ourMemberListExpanded, "ourGroupMembersList", {"displayName", "givenName", "sn", "userPrincipalName"}, {"Display Name", "Given Name", "Surname", "UPN"}),
removeSuperfluous = Table.SelectColumns(membersWithFields, {"Display Name", "Given Name", "Surname", "UPN"})
in
removeSuperfluous