Trial #32: Making OUs More Readable with a Related Table in PowerBI Model

Problem:

I my previous posts I covered connecting PowerBI to AD and forming an custom “OU” column.

The values in this “OU” column do not look great in reports if we want to select our users by OU, it would be preferable to have something neater.

For example: Staff looks better OU=Staff,CN=Users,DC=domain,DC=local

Solution:

You could certainly achieve this by manipulating the string manually or having a series of nested if/then/else statements, matching your OU’s against desired outputs.

However, AD already stores a name for each OU, which will has the great benefit of staying in sync with the user objects we are querying.

Therefore, I chose to create an additional table in the PowerQuery Editor, and then form a relationship between this and my user table in the PowerBI model linked on the distinguishedName of the OU.

The OU table is called organizationalUnit.

Selecting the organizationalUnit table from your domain's ActiveDirectory

Form a many to one relationship between the OU table and the User table

On loading the organizationalUnit from your domain delete superfluous columns “top”, “displayName”, “msExchBaseClass”.

Delete Superfluous Columns Leaving organizationalUnit and distinguishedName

Add a Custom Column that drills down into organizationalUnit and return the list, or the first object in it.

Finally remove the organizationalUnit column.

This produces the following PowerQuery expression

let
    Source = ActiveDirectory.Domains("domain.local"),
    #"domain" = Source{[Domain="domain.local"]}[Object Categories],
    organizationalUnit1 = #"domain"{[Category="organizationalUnit"]}[Objects],
    #"Removed Columns" = Table.RemoveColumns(organizationalUnit1,{"top", "displayName", "msExchBaseClass"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Name", each List.First([organizationalUnit][ou])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"organizationalUnit"})
in
    #"Removed Columns1"

Pitfalls:

This is really straight forward. The one thing that might not be ideal is returning the first item of the list contained in the ou object. There may be some instances where the list contains more than one item and you want them all or one other than the first.

Other Posts in the PowerBI-AD Series:

• Trial #34: AD User Account Status in PowerBI Model
• Trial #33: Querying AD Users Group Membership with PowerBI
• Trial #32: Making OUs More Readable with a Related Table in PowerBI Model
• Trial #31: Transform Active Directory User CN to OU in PowerBI Model
• Trial #30: Accessing Active Directory Users in PowerBI Model