ImagineIT

Retrieving Data from Microsoft Graph in Power BI Using Power Query

Microsoft Graph is a powerful tool for accessing data across Microsoft 365 services. When integrating it with Power BI using Power Query, you unlock the ability to seamlessly fetch data for advanced analytics. This blog post will walk you through the data retrieval process, showcasing how to interact with Microsoft Graph's API efficiently, using authentication and pagination mechanisms in Power Query.

Overview of the Process

To retrieve data from Microsoft Graph in Power BI using Power Query, the key steps are:

1. Authenticate with the Microsoft Graph API using OAuth 2.0.

2. Fetch data using an API endpoint with specific query parameters.

3. Handle pagination to ensure all data is retrieved.

4. Transform the data into a table suitable for reporting.

Let’s delve deeper into each step using the provided code snippet.

1. Authentication

The first step in accessing Microsoft Graph is obtaining an access token using your tenant ID, client ID, and client secret. These credentials are part of your Azure AD app registration.

Below the tenant ID, client ID, and client secret are stored in Parameters

tenantId = TID,
clientId = CID,
clientSecret = CS,
Scope = "https://graph.microsoft.com/.default",

tokenUrl = "https://login.microsoftonline.com/" & tenantId & "/oauth2/v2.0/token",
body = "grant_type=client_credentials&client_id=" & clientId & "&client_secret=" & clientSecret & "&scope=" & Scope,
tokenResponse = Json.Document(Web.Contents(tokenUrl, 
    [
        Content = Text.ToBinary(body),
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
    ])),
accessToken = tokenResponse[access_token],

Here, Power Query sends a POST request to the Microsoft identity platform to exchange credentials for an access token. The token is essential for authenticating subsequent requests to the Microsoft Graph API.

2. API Request

Once authenticated, you can construct API requests to fetch data. The example focuses on fetching user data with specific fields:

API = "https://graph.microsoft.com/v1.0",
What = "users?$top=999&$select=id,displayName,userPrincipalName,assignedLicenses",

The What parameter defines the query to retrieve a batch of 999 users with specific attributes like id, displayName, and assignedLicenses.

3. Handling Pagination

Microsoft Graph often returns data in paginated responses. To ensure all data is retrieved, the code implements a recursive function:

GetGroups = (url) =>
let
    GraphResponse = Json.Document(Web.Contents(API, 
        [
            Headers = [#"Authorization" = "Bearer " & accessToken],
            RelativePath = if url <> null 
                then What & Text.Middle(url, Text.PositionOf(url, "&$skiptoken=")) 
                else What
        ])),
    GraphList = GraphResponse[value],
    NextLink = Record.FieldOrDefault(GraphResponse, "@odata.nextLink", null),
    NextGroups = if NextLink <> null then @GetGroups(NextLink) else {}
in
    List.Combine({GraphList, NextGroups}),

This function:

> Sends an API request to fetch a page of data.

> Checks for the @odata.nextLink property to determine if more data is available.

> Recursively calls itself to retrieve subsequent pages, combining all data into a single list.

4. Transforming the Data

After retrieving the data, Power Query transforms it into a tabular format for Power BI:

GraphTable = Table.FromList(AllGroups, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(GraphTable, "Column1", {"id", "displayName", "userPrincipalName", "assignedLicenses"}),

Further transformations include expanding nested structures, filtering, and cleaning up columns to prepare the data for visualization.

Full Code:

let
    // Define your tenant ID, client ID, and client secret
    tenantId = TID,
    clientId = CID,
    clientSecret = CS,
    Scope = "https://graph.microsoft.com/.default",
    API = "https://graph.microsoft.com/v1.0",
    What="users?$top=999&$select=id,displayName,userPrincipalName,assignedLicenses",

    // Get the access token
    tokenUrl = "https://login.microsoftonline.com/" & tenantId & "/oauth2/v2.0/token",
    body = "grant_type=client_credentials&client_id=" & clientId & "&client_secret=" & clientSecret & "&scope=" & Scope,
    
    // Make the request to get the token
    tokenResponse = Json.Document(Web.Contents(tokenUrl, 
        [
            Content = Text.ToBinary(body),
            Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
        ])),
        
    accessToken = tokenResponse[access_token],

    // Function to get groups with pagination
    GetGroups = (url) =>
    let


        // Make the request to get groups
        GraphResponse = Json.Document(Web.Contents(API, 
            [
                Headers = [#"Authorization" = "Bearer " & accessToken],
                
                RelativePath = if url<>null then What & Text.Middle(url, Text.PositionOf(url, "&$skiptoken=")) else What //null
                
            ])),
        
        // Get the groups from the response
        GraphList = GraphResponse[value],
        
        // Check if there is a next link for pagination
        NextLink = Record.FieldOrDefault(GraphResponse, "@odata.nextLink", null),
        
        // If there is a next link, recursively get the next page
        NextGroups = if NextLink <> null then @GetGroups(NextLink) else {}
    in
        List.Combine({GraphList, NextGroups}),

    // Initial call to the function
    AllGroups = GetGroups(null),

    // Convert the list of groups to a table
    GraphTable = Table.FromList(AllGroups, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
    GraphTable