Forum Discussion

stuart_graham's avatar
stuart_graham
New Member
4 years ago

active directory User Manager

Greetings Power BI dudes   I am trying to build my first report using live data from Active Directory.   I am trying to create a report that maps "displayName" to "manger" when I expand the data ...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

Please provide a more detailed explanation of what you are aiming to achieve. Do  you want to build a company hierarchy or do you only need the name of the immediate manager?

gthill's avatar
gthill
Advocate I
3 years ago

I am trying to achieve this - I am looking to get the displayName of the immediate Manager - I expand the record of the organizationalPerson.manager, but it keeps coming back as Record.

 

What I am looking to get is a table with:

  • User displayName
  • User email
  • User's Manager displayName
  • User's Manager email

Thanks!

Gareth

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    I don't know how your AD is structured but in ours it goes something like this

     

    let
    Source = ActiveDirectory.Domains("domain.net"),
    domain.net = Source{[Domain="domain.net"]}[#"Object Categories"],
    organizationalPerson1 = domain.net{[Category="organizationalPerson"]}[Objects],
    #"Removed Other Columns" = Table.SelectColumns(organizationalPerson1,{"organizationalPerson", "distinguishedName"}),
    #"Expanded organizationalPerson" = Table.ExpandRecordColumn(#"Removed Other Columns", "organizationalPerson", {"mail", "manager"}, {"organizationalPerson.mail", "organizationalPerson.manager"}),
    #"Expanded organizationalPerson.manager" = Table.ExpandRecordColumn(#"Expanded organizationalPerson", "organizationalPerson.manager", {"mail"}, {"organizationalPerson.manager.mail"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded organizationalPerson.manager", each [organizationalPerson.manager.mail] <> null)
in
    #"Filtered Rows"
    • gthill's avatar
      gthill
      Advocate I
      3 years ago

      Perfect - thank you! 😀