Forum Discussion

JLaine's avatar
JLaine
Helper I
4 years ago
Solved

Import Global Address List?

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    JLaine
    Helper I
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        JLaine
        Helper I
        4 years ago

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