cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brianw
Regular Visitor

Active 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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

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:

  1. Getting a list of AD Groups and their members using PowerQuery 
  2. Power BI and Active Directory 

 

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.

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

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

 

 

Syndicate_Admin
Administrator
Administrator

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

 

Syndicate_Admin
Administrator
Administrator

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:

  1. Getting a list of AD Groups and their members using PowerQuery 
  2. Power BI and Active Directory 

 

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.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Top Kudoed Authors
Users online (3,972)