Import Global Address List?

[JLaine]

Hello Power BI people !

As an inexperienced Power BI user, I can't seem to figure this one out so....

How can I import our company's Global Address List (as seen in Outlook and other Office apps) as a table into Power BI?  What power Query would do this?

[Anonymous]

I don't think you'll find a table in the active directory that is specifically address book. However if you go to the User section, then expand Organizational Person, then expand User and Mail. person records you will find everybody's name and email address as a record. Select those columns and records, expand them, and Remove Other Columns. You should then have a column of all names and a column of all email addresses. 

--Nate

--Nate

[JLaine]

Thanks.  I found the person table, and I appears to contain most of what I need, but I also need the department or group that person is in.  I foudn a table named 'msEchangeDepartment' but it was an empty table.  Do you know which table woudl contain this info?

[Anonymous · Accepted solution]

In this M code, replace "CompanyDomain.com" with whatever your domain is, in quotes, except for the Step Name CompanyDomain.com, which is without quotes, and the user step, which CompanyDomain.com is entered without quotes.

let
Source = ActiveDirectory.Domains("CompanyDomain.com"),
CompanyDomain.com = Source{[Domain = "CompanyDomain.com"]}[Object Categories],
user = CompanyDomain.com{[Category = "user"]}[Objects],
#"Removed Other Columns" = Table.SelectColumns(user, {"displayName", "user", "organizationalPerson"}),
#"Expanded organizationalPerson" = Table.ExpandRecordColumn(#"Removed Other Columns", "organizationalPerson", {"department"}, {"department"}),
#"Expanded user 1" = Table.ExpandRecordColumn(#"Expanded organizationalPerson", "user", {"mail"}, {"mail"})
in
#"Expanded user 1"

--Nate

[JLaine]

@Anonymous  Thank you very much.  This is exactly what I needed.  🙂

[Anonymous]

I don't think you'll find a table in the active directory that is specifically address book. However if you go to the person records you will find everybody's name and email address as a record. Select those columns and records, expand them, and you've got all of your organazation's names and email addresses. Done.

--Nate

[Vijay_A_Verma]

You will need to export GAL as csv and then import csv into PQ. This is the only way out currently. 

[Anonymous]

You can use the Active Directory connector  to query the Organizational Person records.

--Nate

[JLaine]

@Anonymous  Thank you.  When I slect "active Directory Connector" in the list of data sources, I see a lot of data tables, but I doo not see 'address book'.  I doo see 'addressBookContainer' but it is an empty table.  What table would contian the address book as seen in Outlook?