Connect Power BI Template to Active Directory

Introduction

Enhancing the data from Anywhere365 with information Active Directory.

Prerequisites

• Have a Power BI file with the Anywhere365 view loaded in (using the template for example)

Quick Setup

Quick setup is useful if you just want department and displayname. If you want to choose specific field or filter you can change the applied steps.

1. Select Get Data

2. Select Blank Query

   

3. Select Advanced Editor

   

4. Copy the following code (change the domain to your own domain)

   Copy

   Query

   let
   Source = ActiveDirectory.Domains("domain.com"),
   domain.com = Source{[Domain="domain.comt"]}[#"Object Categories"],
   contact1 = domain.com{[Category="contact"]}[Objects],
   #"Expanded mailRecipient" = Table.ExpandRecordColumn(contact1, "mailRecipient", {"mail"}, {"mailRecipient.mail"}),
   #"Expanded organizationalPerson" = Table.ExpandRecordColumn(#"Expanded mailRecipient", "organizationalPerson", {"company", "department", "division", "manager"}, {"organizationalPerson.company", "organizationalPerson.department", "organizationalPerson.division", "organizationalPerson.manager"}),
   #"Grouped Rows" = Table.Group(#"Expanded organizationalPerson", {"mailRecipient.mail"}, {{"displayName", each List.Max([displayName]), type text}, {"department", each List.Max([organizationalPerson.department]), type text}, {"manager", each List.Max([organizationalPerson.manager]), type record}}),
   #"Removed Duplicates" = Table.Distinct(#"Grouped Rows", {"mailRecipient.mail"}),
   #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([mailRecipient.mail] <> null)),
   #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"mailRecipient.mail", "MailAddress"}})
   in
   #"Renamed Columns"

5. If this is your first time connection, enter your credentials

6. Click Done

7. (optional) Rename Name

8. Click Close & Apply

9. Open tab Relations

10. Connect PowerBI_Dialogue.initialagent with Query1.MailAddress