Posted by In my previous blog post ‘Hidden treasures: Use Excel data types from Power BI‘ I wrote about connecting to local Active Directory from Power BI, but would it also be possible to connect to Azure Active Directory? Well, there’s no Power Query connector for Azure Active Directory (yet?) and connecting to Microsoft Graph isn’t recommended, but there’s a (virtual) table ‘AAD User’ in Dataverse that we can use for this purpose. Unfortunately virtual tables are not supported in the Dataverse connector so we need to use the OData connector instead. The API link to the table data can be accessed and copied via the Tools option of the AAD User table: https://[Organization URI].crm.dynamics.com/api/data/v9.2/aadusers
This is the url for the OData feed, and with OAuth2 authentication based on an organizational account.
The users can be filtered on Members only to limit the number of rows.
In the Model view the table can be set up as ‘Is featured table’.
Publish to Power BI service and sign in with an organizational (service) account to update the data source credentials in the settings of the published dataset. The scheduled refresh can also be enabled here.
Open Excel and use the AAD User data type to resolve the e-mail address and insert extra data.
This is a cloud-only solution, so no need for data gateway and local Active Directory. Every cloud has a silver lining 😉