Product
PricingCommunity

Organization Chart using Microsoft Graph API

Microsoft Entra ID (formerly Azure Active Directory) helps IT teams manage users, groups, and resources using the Microsoft Graph API. It enables the creation of organizational charts while providing insights into resource access, like applications and shared drives. This ensures efficient access management and security for company employees.

This guide demonstrates how to import data from Microsoft Entra ID (Formerly Azure Active Directory) into Power BI using Power Query and Microsoft Graph API. After importing the data, we can visualize it using the xViz Performance Flow in Power BI.

To visualize Microsoft Entra ID (formerly Azure Active Directory) data in xViz Performance Flow, we follow this process:

  1. Prerequisites

  2. Initialize Parameters

  3. Query to fetch Microsoft Entra ID (Formerly Azure Active Directory) Data in Power Query

  4. Query to fetch the images

  5. Visualize the data using the xViz Performance Flow visual

1. Prerequisites:

To securely access Microsoft Entra ID (formerly Azure Active Directory) data within Power BI, the following setup is required:

  1. Azure Tenant ID: This is the unique identifier for your organization within Azure.

  2. AAD Application: An application must be registered in Azure Active Directory to access AAD data securely. Permissions are necessary for accessing specific data, which can be found in the Microsoft Graph Permissions Reference.

  3. AAD App Credentials:

    • Azure Application Client ID: This identifier is assigned to the application registered for accessing AAD data.

    • Azure Application Client Secret: A secure password for the application, granting it access to AAD.

These credentials will enable the AAD app to connect to the Microsoft Graph API, managing user data in AAD.

2. Initialize Parameters:

  1. First, create three parameters in Power Query. To create new parameters, navigate to the Home tab and click on Manage Parameters.

  1. Create parameters for Azure Tenant ID, Azure Application Client ID, and Azure Application Client Secret, which are essential for fetching data from Microsoft Entra ID (Formerly Azure Active Directory). Populate the parameters with the respective values as shown image below:

3. Query to fetch Microsoft Entra ID (Formerly Azure Active Directory) Data in Power Query:

  1. Now, we’ll create a blank query and rename the query to "AAD Active Employee". Then navigate to the Advanced Editor. Paste the following M code into the editor and click on 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", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
in
    Source

  1. After connecting, you will need to edit the credentials and set the appropriate privacy levels to ensure secure data access.

    • First, Select Anonymous as the authentication method, as the necessary Client ID and Client Secret have already been provided through the Azure application setup.

    • Next, you’ll be prompted to configure Data Privacy settings. Set the privacy level for both https://login.windows.net/ and https://graph.microsoft.com/ to Organizational. Once completed, click Save to apply these settings and proceed with the data import.

This step is essential for protecting sensitive information and ensuring that your data remains secure within your organization’s boundaries.

  1. After a short wait, your first set of Microsoft Graph data should now appear in Power Query, ready for you to explore and transform!

4. Query to fetch the images:

  1. Now, we'll fetch the images from the Microsoft Entra ID (Formerly Azure Active Directory). First, we’ll create a blank query and navigate to the Advanced Editor. Paste the following M code into the editor and then rename the query to "AAD Image". This step initializes the setup for fetching images from Azure Active Directory (AAD).

let
    // Step 1: Get Access Token
    tokenResponse = Json.Document(
        Web.Contents(
            "https://login.microsoftonline.com/" & #"Azure AD Tenant ID" & "/oauth2/v2.0/token",
            [
                Content = Text.ToBinary(
                    Uri.BuildQueryString(
                        [
                            client_id = #"Azure Application Client ID",
                            client_secret = #"Azure Application Client Secret",
                            grant_type = "client_credentials",
                            scope = "https://graph.microsoft.com/.default"
                        ]
                    )
                ),
                Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
            ]
        )
    ),
    accessToken = tokenResponse[access_token],

    // Step 2: Get user IDs
    userEndpoint = "https://graph.microsoft.com/v1.0/users?$select=id&$top=999 ",
    usersResponse = Json.Document(
        Web.Contents(
            userEndpoint,
            [
                Headers = [Authorization = "Bearer " & accessToken]
            ]
        )
    ),

    userItems = usersResponse[value],
    userIds = List.Transform(userItems, each _[id]), 

    baseEndpoint = "https://graph.microsoft.com/v1.0/users/",

    // Step 3: Define function to Get User Photos
    getPhoto = (userId) =>
        let
            photo = Web.Contents(
                baseEndpoint & userId & "/photo/$value",
                [
                    Headers = [Authorization = "Bearer " & accessToken]
                ]
            )
        in
            photo,

    // Step 4: Get User Photos
    userPhotos = Record.FromList(
        List.Transform(userIds, getPhoto),
        userIds
    )
in
        userPhotos

  1. After a few minutes, we will be able to see two columns with the employee ID and their images as Base64URLs in Power Query. Then, you can change the data type of both columns to text. And change the column name “Name” to “Id” & “Value” to “Image”.

  1. Create a custom column combining Base64 image data with the data URI scheme, use this formula in Power Query:

Employee Image = "data:image/png;base64," & [Image]

This combines the string "data:image/png;base64," with the Base64-encoded image data in the [Image] column for use in Power BI visuals.

The Base64 image URL needs to be prefixed with "data /png;base64" for proper display.

  1. After creating the custom column, remove the "Image" column and change the data type of the "Employee Image" column to Text.

  1. Click Close & Apply to save your changes and then establish a relationship between the previously imported employee data using the Id column.

PreviousMicrosoft Tenant Data AnalyticsNextProcess Flow

Last updated

Was this helpful?