Easily connecting between Power Query / Power BI and Microsoft Graph

A while ago I was experimenting with Power Query in Microsoft Excel, and was excited by the functionality that allows you to import live data into an Excel spreadsheet from Active Directory.

I wanted to also be able to import the same data from Azure Active Directory in the cloud, but it’s not easy. The basic issue is that the latest API for accessing this, Microsoft Graph, uses the OAuth 2.0 authentication flow to connect, which normally means you need a custom connector specifically written for Power Query / Power BI before we can easily access it. A few of proposed solutions include creating an Azure function or a custom PowerBI connector but each of these requires Visual Studio: I wanted a way to do it without needing to deploy any extra code. This guide will talk you through how to do it without any functions or connectors; you only need access to Azure Active Directory.

A big acknowledgement and ‘thank you’ to a number of people who have pointed the way, including ruiromano and jevgenijmart from the Microsoft Power BI Community for the initial idea, Chris Koester for helping me understand exactly how their solutions worked and how to avoid a Power Query Data Privacy ‘Formula.Firewall’ issue by combining them into a single query, Lucian again from the Microsoft Power BI Community for the code that gets around an annoying ‘The feed’s metadata document appears to be invalid’ error, Vasil Michev for the pointer to the new Exchange Online REST API link, Marc LaFleur from the ever helpful Stack Overflow describing how to link Power Query / Power BI with the activity reports available via Microsoft Graph, Mynda Treacy for the tip on speeding up Excel by preventing data previews from being downloaded in the background, David Grooms for info on using paging in the Graph API for large datasets, and Chris Webb for the trick allowing refreshing of datasets when publishing to the Power BI service. However any errors or omissions are my own.

All of the instructions below are for the version of Microsoft Excel that comes with Microsoft 365 apps for enterprise; however, it should be simple to translate these steps into PowerBI.

Please note that connecting to the Microsoft Graph REST APIs from Power Query ‘isn’t recommended or supported’ by Microsoft, although they don’t appear to have provided any ‘out of box’ alternatives at this stage which is a shame.

Register a new application in Azure Active Directory

The first thing you need to do is set up a new ‘application’ inside your Azure Active Directory which will define what level of access your Power Query / Power BI query will have to the information in Microsoft Graph. This will involve setting up a new Client ID and Client Secret. You may also need Global Administrator or equivalent privileges on your Azure Active Directory to do this.

NB You should choose the permissions carefully, and not disclose the Client ID and Client Secret to untrusted people; they act as a username and password allowing access to whatever grant permission to anyone who has them. You should also be careful where you save any documents based on the queries below since the Power Query parameters will contain the Client ID and Client Secret.

• Open the App registrations area of Azure Active Directory (https://aad.portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps) and select New registration.

• Enter the following information, then click Register:
  - Name: <Enter a name for your application e.g. ‘Company Name Azure Active Directory Power Query’>
  - Supported account types: ‘Accounts in this organizational directory only’
  - Redirect URI (optional): Leave blank

• Once the application Overview appears, make a note of the following values for later on:
  - Application (client) ID (this defines this application)
  - Directory (tenant) ID (this defines your Azure Active Directory)

• Select Certificates & secrets, then under Client secrets, select New client secret

• Enter the following information, then click Add:
  - Description: <Enter a description for this client secret e.g. the name of the document you will use to store it>
  - Expires: Set according to your security needs: either Never if you are sure you will never want to require renewal, or 1 year / 2 years if you want it to automatically expire.

• Make a note of the Value of the new Client secret for later on (the ID of the new Client secret is not required for this process):

• Select API permissions, then under Configured permissions, select Add a permission

• Select Microsoft Graph, then Application permissions, select the permissions you need, then click Add permissions. (NB I was unable to get Power Query to work using Delegated permissions).
  Which permissions you should add will depend on what data you want to grant Power Query access to. You can use the Microsoft Graph Explorer tool to preview the kind of data that is available; use the Modify Permissions button to see the permissions you might need to grant. For this example, you will need to grant Directory.Read.All and Reports.Read.All. You should read more about Microsoft Graph permissions and assign only what is necessary; for example, for the purposes described here there is no need to assign any ReadWrite permissions as Power Query is currently unable able to write data back to Azure Active Directory, and you would be opening up the risk of an attacker making changes to your Azure Active Directory if they got hold of your Application Client ID and Application Client secret.

• Make sure you can see all the permissions you previously added under Configured permissions, then click Grant admin consent for <Company name>:

• You should see each permission go green; this is the final step.

NB I cannot emphasise enough how important it is to select the correct permissions, and keep the Client ID and Client secret secure. If you manage to open up your system too much and you get compromised, that’s on you!

Creating Power Query parameters in Excel

Now let’s break out Excel and start setting up Power Query.

• Open Excel, and under the Data tab, click Get Data / Launch Power Query Editor…

First, we’ll change the Background Data options to make sure the slow nature of Microsoft Graph queries don’t cause slow downs or hangs.

• Under the File menu, click Options and settings / Query Options:

• Under Current workbook, click Data Load, then under Background Data, disable Allow data previews to download in the background, then click OK.

This means that background queries and refreshes are disable since the Power Queries we’ll be running are quite slow and can make Microsoft Excel unresponsive if data preview background downloads are attempted.

Now we’ll create some parameters to store the queries we just generated.

• Under the Home tab, click Manage Parameters / New Parameter:

• Add new parameters along these lines, then select OK. These can also be hard-coded into your individual Microsoft Graph API queries but you might need to change them later so it’s better to just set them once here!:
  - Name: Azure AD Tenant ID
  - Current Value: <your Azure AD Tenant ID from above>
• In the same dialog, click New to add another parameter:
  - Name: Azure Application Client ID
  - Current Value: <your Azure Application Client ID from above>
• Click New to add another parameter:
  - Name: Azure Application Client Secret
  - Current Value: <your Azure Application Client Secret from above>

Creating a Power Query for normal data in Excel

Now we’re ready to create a query which returns some data which Microsoft Graph returns in list format (e.g. users):

• Go back to the Power Query Editor; under the Queries area, right-click and select New Query / Other Sources / Blank Query.

• Right click the new Query and rename it to reflect the data you’re trying to collect (e.g. ‘Microsoft Graph Users’), then under the Home tab, click Advanced Editor.

• Replace the entire current contents of Advanced Editor with the following Power Query M formula, then click Done:

let 
    resource="https://graph.microsoft.com",
    tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
    [
        RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
        Content = Text.ToBinary(Uri.BuildQueryString(
            [
                client_id = #"Azure Application Client ID",
                resource = resource,
                grant_type = "client_credentials",
                client_secret = #"Azure Application Client Secret"
            ]
        )),
        Headers = [Accept = "application/json"], ManualStatusHandling = {400}
    ])),
    access_token = tokenResponse[access_token],
    Source = OData.Feed("https://graph.microsoft.com/beta/users?$top=120&$select=id, deletedDateTime, accountEnabled, AgeGroup, assignedLicenses, assignedPlans, businessPhones, city, companyName, consentProvidedForMinor, country, createdDateTime, creationType, department, deviceKeys, displayName, employeeHireDate, employeeId, employeeOrgData, employeeType, externalUserState, externalUserStateChangeDateTime, faxNumber, givenName, identities, imAddresses, infoCatalogs, isResourceAccount, jobTitle, lastPasswordChangeDateTime, legalAgeGroupClassification, licenseAssignmentStates, mail, mailNickname, mobilePhone, officeLocation, onPremisesDistinguishedName, onPremisesDomainName, onPremisesExtensionAttributes, onPremisesImmutableId, onPremisesLastSyncDateTime, onPremisesProvisioningErrors, onPremisesSamAccountName, onPremisesSecurityIdentifier, onPremisesSyncEnabled, onPremisesUserPrincipalName, otherMails, passwordPolicies, passwordProfile, postalcode, preferredDataLocation, preferredLanguage, provisionedPlans, proxyAddresses, refreshTokensValidFromDateTime, showInAddressList, signInSessionsValidFromDateTime, state, streetAddress, surname, usageLocation, userPrincipalName, userType", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
in
    Source

This query uses Power Query’s Web.Contents API to obtain an OAuth 2.0 access token (access_token) via the Microsoft identity platform via the client credentials grant method. It then uses Power Query’s OData.Feed API to request data from Microsoft Graph, using the access token as part of an ‘Authorization: Bearer’ HTTP header to authenticate.
(Modified 18/2/2021 — added ExcludedFromCacheKey = {“Authorization”} to mitigate throttling issues, removed Concurrent = true)
(Modified 22/3/2021 — added $top=999& to set a larger paging value so that larger data sets can be downloaded more efficiently — this may need to be modified for resources other than ‘user’ as each Microsoft Graph resource can have a different ‘maximum’ page size)
(Modified 5/4/2022 — changed $top=999& to $top=120& to work around new Microsoft Graph limitation — https://github.com/microsoftgraph/msgraph-sdk-powershell/issues/1898)
(Modified 18/5/2023 — changed tokenResponse Json.Document(Web.Contents()) to using RelativePath option to allow refreshing when publishing to Power BI service)

• You will be prompted to Edit Credentials:

• You have supplied the necessary credentials with the Azure Application Client ID and Azure Application Client Secret above, so make sure Anonymous is selected, and click Connect (you may need to do this for both ‘https://login.windows.net/’ and ‘https://graph.microsoft.com/beta/users’):

• You will be prompted that ‘Information is required about data privacy’. Select Continue:

• You should read more about Privacy Levels here; however, for the time being, set privacy levels for both ‘https://login.windows.net/’ and ‘https://graph.microsoft.com/’ to Organizational, then click Save:

• After a few minutes, you should now be able to see your first set of Microsoft Graph data in Power Query! You may need to expand Tables to gain access to additional data, remove any columns you don’t need from $select statement via the Advanced Editor (see below), and replace any Errors; but once you have everything you need, click Close & Load to complete the setup!

NB With the Microsoft Graph 1.0 API endpoint, certain APIs like ‘users’ and ‘groups’ only return a limited set of properties if you do not set the query parameter $select. If you miss out everything in bold in the Power Query M Formula above (between ?$top and the double quotes”) you will be able to see a list of all the properties, and to speed up the query process make sure you only leave the ones you need in the list above; but in order to get the query to actually work I had to specify the ones I actually wanted to avoid the error ‘We expected a property ‘[propertyname]’, but the OData service omitted it from the response data’. I also found certain properties towards the end of the list (such as ‘mailSettings’ threw the error ‘(501) Not Implemented. (This operation target is not yet supported)’ if I tried to include them in the $select statement at this point in time.

Setting up Data source credentials in the Power BI Service

You may wish to set up new queries using Microsoft Graph inside Power BI and refresh them inside the Power BI service. This is possible, but make sure when you are setting up Data source credentials in the cloud service for ‘https://graph.microsoft.com/’ to tick ‘Skip test connection’ to work around the ‘Failed to update data source credentials: The credentials provided for the OData source are invalid.’ error.

Creating a Power Query for Exchange Online data in Excel

You may also need to import some Exchange Online data not currently available in Microsoft Graph, especially from the Exchange Admin Centre (lists of mailboxes & mailbox types, recipients data etc).

At present this is quite easy to do (although it’s in beta, so don’t depend on it!) with the new Exchange Online REST API. Under the Power Query Editor, go to the Queries area, right-click and select New Query / Other Sources / OData feed. Enter the following URL:

https://outlook.office.com/adminApi/beta/<your Azure AD Tenant ID from above>/

You can then log in with an Organizational account, then Sign in using your Microsoft 365 credentials, and click Connect:

You can then see a list of the possible tables such as ‘Mailbox’, ‘CasMailbox’, ‘DynamicDistributionGroup’, ‘UnifiedGroup’, ‘User’ and ‘Recipient’, in the usual ‘Navigator’ view.

When you want to access one of these tables, create a new OData feed with the following URL:

https://outlook.office.com/adminApi/beta/<your Azure AD Tenant ID from above>/<table name>?$select=columnname1, columnname2

to import the table including a named set of columns, or:

https://outlook.office.com/adminApi/beta/<your Azure AD Tenant ID from above>/<table name>?PropertySet=All

to import the table including all available columns.

Creating a Power Query for report data in Excel

That should be enough to play with most of the Microsoft Graph APIs. However, there are some APIs which don’t work with the above steps: this may be because they return data as an HTTPS link to a CSV file instead of OData (e.g. reports). To create a query which picks up data in this format:

• Go back to the Power Query Editor; under the Queries area, right-click and select New Query / Other Sources / Blank Query.

• Right click the new Query and rename it to reflect the data you’re trying to collect (e.g. ‘Microsoft Graph Active Users’), then under the Home tab, click Advanced Editor.

• Again, replace the entire current contents of Advanced Editor with the following Power Query M formula, then click Done:

let 
    resource="https://graph.microsoft.com",
    tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
    [
        RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
        Content = Text.ToBinary(Uri.BuildQueryString(
            [
                client_id = #"Azure Application Client ID",
                resource = resource,
                grant_type = "client_credentials",
                client_secret = #"Azure Application Client Secret"
            ]
        )),
        Headers = [Accept = "application/json"], ManualStatusHandling = {400}
    ])),
    access_token = tokenResponse[access_token],
Source = Csv.Document(Web.Contents("https://graph.microsoft.com/beta/reports/getOffice365ActiveUserDetail(period='D7')", [ Headers = [ Authorization = "Bearer " & access_token ], ExcludedFromCacheKey = {"Authorization"} ] )),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

(Modified 18/2/2021 — added ExcludedFromCacheKey = {“Authorization”} to mitigate throttling issues)
(Modified 18/5/2023 — changed tokenResponse Json.Document(Web.Contents()) to using RelativePath option to allow refreshing when publishing to Power BI service)

• Again, you will be prompted to Edit Credentials:

• Again, make sure Anonymous is selected, and click Connect (you will need to do this for ‘https://graph.microsoft.com/beta/getOffice365ActiveUserDetail’):

• Again, you will be prompted that ‘Information is required about data privacy’. Select Continue:

• Set the privacy level for ‘https://graph.microsoft.com/’ to Organizational, then click Save.

• After a few minutes, you should see the report data appear. Right click and select Change Type on any relevant columns to Date / True/False / Whole Number etc, then complete the setup using Close & Load!

I hope this helps other people as they manage Microsoft Graph data using Power Query / Power BI. Do send a response if you have any questions or suggestions about how to improve this process!

Legal notice: This documentation is provided without any warranty; without even the implied warranty of merchantability or fitness for a particular purpose. In no event shall the author or contributors be liable for any damages arising from its use. This documentation is provided “as is”.