Forum Discussion
AdministratorActive Directory query group membership for a user
I would like to use Power Query (within Excel) to interrogate Active Directory.
Given a list of userids in a worksheet, is it possible to lookup each one in AD and test whether they are a member of a specific group?
Hi @brianw,
You can connect to Azure Directory in Power Query and select the 'Group' table to get the AD groups names.
Steps:
Click the Data tab, then select New Query > From Other Sources > From Active Directory
After you’ve connected to your Active Directory, you’ll be able to navigator, your be able to select your domain from your forest and then select the object you want to query. For this example, select your domain then select group, then click Edit.
This will then list out the groups, or at least a sample. Click on the left\right arrow on securityPrincipal column header, this then bring up a filter window, select sAMAccountName and click on OK. This will give you the friendly name your more then likely to know the groups by.
Please refer:
In addition, you can try this query to replace the following M syntax in your file for the table called AD User & Groups to get groups and users:
let Source = ActiveDirectory.Domains(#"Domain Name"), my.domain.com = Source{[Domain=#"Domain Name"]}[#"Object Categories"], person1 = my.domain.com{[Category="person"]}[Objects], #"Expanded top1" = Table.ExpandRecordColumn(person1, "top", {"memberOf"}, {"top.memberOf"}), #"Expanded top.memberOf2" = Table.ExpandListColumn(#"Expanded top1", "top.memberOf"), #"Expanded top.memberOf3" = Table.ExpandRecordColumn(#"Expanded top.memberOf2", "top.memberOf", {"displayName", "name"}, {"top.memberOf.displayName", "top.memberOf.name"}), #"Filtered Rows" = Table.SelectRows(#"Expanded top.memberOf3", each ([displayName] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"person", "distinguishedName"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"displayName", "Display Name"}, {"top.memberOf.displayName", "Group Email Address"}, {"top.memberOf.name", "Group Name"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Display Name"},#"AD - User Details",{"Display Name"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"User Principal name", "First Name Letter", "Surname Letter"}, {"User Principal name", "First Name Letter", "Surname Letter"}) in #"Expanded NewColumn" #"Expanded NewColumn"Refer: Appendix: Adding in the AD User & Groups
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
This link might send you in the right direction, although it probably has to be purchased.
https://www.cdata.com/kb/tech/activedirectory-odbc-excel-query.rst
If an excel adapter is not an option, I'd personnaly do this with a powershell script. These two links combined would probably be a good start.
Get-ADGroupMember (ActiveDirectory) | Microsoft Docs
https://stackoverflow.com/questions/59402365/update-a-cell-in-a-excel-sheet-using-powershell
- mahoneypat
Microsoft Employee
This would be much easier in Power Automate, either directly or with the Power Automate visual from Power BI. If a stand-alone flow, you could list rows in an Excel table and then check group membership in an Apply to Each step (or you could filter the array based on group membership I believe). From there you could create the file somewhere and trigger refresh of Power BI dataset to ingest that file if needed.
Pat
v-yingjlCommunity Support
Hi @brianw,
You can connect to Azure Directory in Power Query and select the 'Group' table to get the AD groups names.
Steps:
Click the Data tab, then select New Query > From Other Sources > From Active Directory
After you’ve connected to your Active Directory, you’ll be able to navigator, your be able to select your domain from your forest and then select the object you want to query. For this example, select your domain then select group, then click Edit.
This will then list out the groups, or at least a sample. Click on the left\right arrow on securityPrincipal column header, this then bring up a filter window, select sAMAccountName and click on OK. This will give you the friendly name your more then likely to know the groups by.
Please refer:
In addition, you can try this query to replace the following M syntax in your file for the table called AD User & Groups to get groups and users:
let Source = ActiveDirectory.Domains(#"Domain Name"), my.domain.com = Source{[Domain=#"Domain Name"]}[#"Object Categories"], person1 = my.domain.com{[Category="person"]}[Objects], #"Expanded top1" = Table.ExpandRecordColumn(person1, "top", {"memberOf"}, {"top.memberOf"}), #"Expanded top.memberOf2" = Table.ExpandListColumn(#"Expanded top1", "top.memberOf"), #"Expanded top.memberOf3" = Table.ExpandRecordColumn(#"Expanded top.memberOf2", "top.memberOf", {"displayName", "name"}, {"top.memberOf.displayName", "top.memberOf.name"}), #"Filtered Rows" = Table.SelectRows(#"Expanded top.memberOf3", each ([displayName] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"person", "distinguishedName"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"displayName", "Display Name"}, {"top.memberOf.displayName", "Group Email Address"}, {"top.memberOf.name", "Group Name"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Display Name"},#"AD - User Details",{"Display Name"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"User Principal name", "First Name Letter", "Surname Letter"}, {"User Principal name", "First Name Letter", "Surname Letter"}) in #"Expanded NewColumn" #"Expanded NewColumn"Refer: Appendix: Adding in the AD User & Groups
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
