Forum Discussion

Evogelpohl's avatar
Evogelpohl
Icon for Helper V rankHelper V
10 years ago

Active Directory - Get Members of Dynamic Group

Using the Active Directory (AD) connector in Power Query (latest ver), I'm able to view all groups, but i'm not able to 'Expand' to Group.Member on AD groups that are Dynamic, or those groups that use LDAP queries to populate them.

 

Is this a feature/limitation of AD or a bug perhaps?

 

Here's the code that should return all users in a group.   

 

let
    Source = ActiveDirectory.Domains("DOMAIN.com"),
    DOMAIN.com = Source{[Domain="DOMAIN.com"]}[#"Object Categories"],
    group1 = DOMAIN.com{[Category="group"]}[Objects],
    #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded securityPrincipal", each [securityPrincipal.sAMAccountName] = "ALL-EmplsAndContractors"),
    #"Expanded group" = Table.ExpandRecordColumn(#"Filtered Rows", "group", {"member"}, {"group.member"}),
    #"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member")
in
    #"Expanded group.member"

3 Replies