Get members of a security group in Power BI using Power Query

You may already know that there is an existing connector to your on-premise Active Directory

Get Data > Active Directory

But what if you only need to get a list of users from a specific security group? Simply use the Power Query script below and change the domain name and the yourSecurityGroupName. You can expand the group.member if you are interested in any of the other fields 😎! Hope this helps!

let
 Source = ActiveDirectory.Domains("contoso.com"),
 DOMAIN.com = Source{[Domain="contoso.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] = "yourSecurityGroupName"),
 #"Expanded group" = Table.ExpandRecordColumn(#"Filtered Rows", "group", {"member"}, {"group.member"}),
 #"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"),
 #"Expanded group.member.name" = Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"name"}, {"name"})
in
 #"Expanded group.member.name"

Please note this will only work for security group with direct memberships.