Getting computer OU information

[KMKing]

I am pretty new to Power BI, but its pretty awesome! I have a work in progress dashboard that has simple active directory information. I am trying to get the computer OU information so I can display that, and filter based on OU.

The distinguishedName has that info, but I do not know for the life of me how to make it "readable" and not contain all the CN=,OU=,OU=,DC=,DC=,DC= stuff. I am sure it is something right under my nose and I am going to feel silly once someone points it out.

Example:

CN=Server123,OU=Prod,OU=Servers,DC=help,DC=me,DC=out

To something like:

Prod/Servers

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[KMKing]

Sorry, I posted in haste.

I am using the Active Directory connector in Power BI to get information on servers in our domain. I would like to display the servers OU information also on this report. I can then create a filter to show a count of servers in certain OUs. I just need the OU information to be in a better format than what is in the distinguishedName colum.

If I add that to the report as it sits, it has all the LDAP information included.

Example: CN=Server123,OU=Prod,OU=Servers,DC=help,DC=me,DC=out

Format desired: Prod/Servers

Hope that makes more sense. 

[KMKing · Accepted solution]

Was able to resolve this on my own. 

[az38]

@KMKing 

please, share your solution 🙂

[KMKing]

I split the column using custom delimiters to break out the information and then replaced the text.

Split column

Custom Delimiter - CN=, (left most)

Then proceeded to do the same for the end (DC info)

Split the new column off again - Custom Delimiter - DC=, (left most)

This left me with OU=something,OU=something

I replaced the text on that colum to change OU= to a "/"

This left me with a column containing the OU location only. I will do this again with pictures, I know it seems a little hard to understand with just me typing it out.

[ZosiaBoj]

I don't know if anyone else is still looking at this 4 years on but Bing brought me here searching to do similar.

I couldn't work out how the OP got the desired text using split but it got me thinking and instead of doing two splits, I went to the Transform ribbon and selected Extract > "Text Between Delimiters" and then from "," to ",DC="

Then split this into rows on ",". This left me with one row starting "CN=", where ther emust have been no OU, so I filtered on rows beginning "OU=". (I guess I could also have just split on "," and then done this step also)

Then used Replace Values to replace "OU=" with "".

Finally tidied up by deleting the additional columns that were created during these steps.

Now I have a tidy lookup table of computers and there OUs.