PowerBI to Query AD Group Memberships

Hi  v-alq-msft ,

There is an active directory connector in Get Data from PowerBI Desktop.  I was able to solve my issues. The first step:  I selected these tables from the AD connector AD: Group, InetorgPerson, User.  The next step was to expand the right columns.

Group Table

expand column Member and select these fields: Display Name, Member Of, Department,  EduPersonPrimary, SamAccountName, User Account Control,

InetorgPerson Table

Expand column EduPerson and select this fields: EduPersonPrimaryAffiliation

Expand column SamAccountName and select this field: Security Principal

Expand column  OrganizationalPerson and select these fields: Department, Division, Given Name, Title

User Table

Expand column: User and select these fields:  DepartmentNumber,  UserPrincipalName, UserAccountControl.

Expand column: Person and select this field:  SN (this is the surname attribute)

Expand column: SecurityPrincipal and select this field: SamAccountName

I linked all the tables: Group, InetorgPerson, and User using SamAccountName

My next issue was filtering active and inactive accounts.  I read another post by niark Solved: AD useraccountcontrol integer conversion - Microsoft Power BI Community to figure out the integer conversion for the user account control. 

I opted to create a spreadsheet with the converted user account integers

The last step was to link the user table and my spreadsheet using UACProperties, then group active account and disabled accounts.

I use the UACProperties to filter the active accounts on each report page.

In my daily tasks as an enterprise desktop admin, I regularly run powershell applets to get AD user group membership and add or remove users from groups.  PowerBI has allowed me to get a much bigger picture of the users, which groups they belong to and discover issues across my organization.

Charles