Hidden treasures: Use Excel data types from Power BI

In the Billing section of the Microsoft 365 admin center there’s an option to export a list of users that are assigned to a specific license (Billing > Licenses > License: Export users). In the exported csv file the following user details are listed:

Block credential,City,Country/Region,Department,DirSyncEnabled,Display name,Fax,First name,Last dirsync time,Last name,Last password change time stamp,License assignment details,Licenses,Mobile Phone,Oath token meta data,Object Id,Office,Password never expires,Phone number,Postal code,Preferred data location,Preferred language,Proxy addresses,Release track,Soft deletion time stamp,State,Street address,Strong password required,Title,Usage location,User principal name,When created

This can be helpful when you want to charge the license costs to budget holders, based on the Department, Office or Usage location for example. But we also have applications (on-premises, cloud) that have their own user and license management and you end up with pasting a list of display names and e-mail addresses into an Excel sheet and do some VLOOKUP magic to get the company or department from a sheet with users exported from the Active Directory. You can -of course- automate this with Power Automate flow using the Office 365 Users connector but there’s another (smarter?) way of doing this: use Excel data types from Power BI.

In Power BI Desktop we can Get Data from the Active Directory as source and enter the name of a local domain.

Let’s start from scratch and Get Data from a Blank Query; rename the query to ‘users’ (this name will be shown in Excel). Add a parameter named ‘Domain’ in Power Query Editor and set your local domain name as default value. Copy the following code and paste it in the Advanced Editor of Power Query.

let
    Source = ActiveDirectory.Domains(Domain),
    domain.local = Source{[Domain=Domain]}[#"Object Categories"],
    user1 = domain.local{[Category="user"]}[Objects],
    #"Expanded user" = Table.ExpandRecordColumn(user1, "user", {"givenName", "mail", "manager", "mobile", "userPrincipalName"}, {"user.givenName", "user.mail", "user.manager", "user.mobile", "user.userPrincipalName"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded user", each ([displayName] <> null)),
    #"Expanded user.manager" = Table.ExpandRecordColumn(#"Filtered Rows", "user.manager", {"displayName"}, {"user.manager.displayName"}),
    #"Expanded organizationalPerson" = Table.ExpandRecordColumn(#"Expanded user.manager", "organizationalPerson", {"c", "co", "company", "department", "employeeID", "l", "physicalDeliveryOfficeName", "title"}, {"organizationalPerson.c", "organizationalPerson.co", "organizationalPerson.company", "organizationalPerson.department", "organizationalPerson.employeeID", "organizationalPerson.l", "organizationalPerson.physicalDeliveryOfficeName", "organizationalPerson.title"}),
    #"Expanded person" = Table.ExpandRecordColumn(#"Expanded organizationalPerson", "person", {"telephoneNumber"}, {"person.telephoneNumber"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded person",{"displayName", "user.givenName", "user.mail", "user.manager.displayName", "user.mobile", "user.userPrincipalName", "organizationalPerson.c", "organizationalPerson.co", "organizationalPerson.company", "organizationalPerson.department", "organizationalPerson.employeeID", "organizationalPerson.l", "organizationalPerson.physicalDeliveryOfficeName", "organizationalPerson.title", "person.telephoneNumber"})
in
    #"Removed Other Columns"

This will get the users from your local domain. You can add extra steps to filter the data, etc. Select Close & Apply to add the table to the dataset. In the Model view you select the table and set ‘Is featured table’ to Yes. Set the Description, Row label and Key column, and hit the Save button.

Publish to Power BI and select a workspace to share this featured table with users that have access to that workspace.

Because the Power BI Service must be able to connect to your local Active Directory, you need to make use of the data gateway and add/map the data sources (with Windows authentication) in the settings of the dataset.

In the same dataset settings the Scheduled refresh can be enabled.

It might take a while before the published data type is available in Excel.
Open Excel and select a cell with an e-mail address. Go to Data > Data Types > From your organization: select ‘users’

If the e-mail address is found in the featured table it will be resolved, and other columns from the table can be added to the Excel sheet. You can click on the briefcase icon to show a card with the data from the row in the featured table.

In this way the user data can be enriched and used for reporting, with less effort.

There are prerequisites to be able to use data types from Power BI:

Notes: In order to access and use data types from Power BI, you must meet the below requirements. If you do but still don’t see the feature, please contact your IT admin for help.

• You’re an Excel subscriber in organizations with Microsoft 365 and the Power BI Pro service plan.
• You have the latest version of the Excel app on either Current Channel or Monthly Enterprise Channel. For more information, see Overview of update channels for Microsoft 365 Apps. Support for Excel for the web and Excel for Windows on the Semi-Annual Enterprise Channel will be coming soon.
• The English editing language is added to your Office Language Preferences.
• Power BI featured tables are enabled in Power BI for data types in Excel, and you have access to them. For more info, see Power BI considerations and limitations.
• Your Power BI admin has enabled featured tables in your organization.

Source: Notes from Convert text to an Organization data type.

For a cloud-only solution based on Azure Active Directory, see my next blog post: Use Dataverse table ‘AAD User’ for Excel data types from Power BI.