Power BI Advanced — Build a Custom Data Connector for Microsoft Graph API

Power BI Advanced Series · Data Gateway & Enterprise Integration · by Raushan Ranjan, MCT

Power BI is a fantastic tool for data visualization and analysis, but sometimes you need to connect to data sources that aren't available out-of-the-box. This is where custom data connectors come in! This guide will walk you through creating a custom connector for the Microsoft Graph API, allowing you to bring in rich organizational data—like users, emails, calendars, Teams information, and more—directly into your Power BI reports. This is a step-by-step, beginner-friendly guide with concepts and purpose explained simply.

🔍 What Is a Custom Data Connector in Power BI?

Power BI comes with a vast array of built-in connectors for common data sources like Excel files, SQL Server databases, SharePoint lists, and various cloud services. However, when you want to fetch data from a specific API (Application Programming Interface) that isn't pre-integrated, such as the Microsoft Graph API, you'll need to develop a custom connector. A custom connector is essentially a piece of M language code that tells Power BI how to connect, authenticate, and retrieve data from your chosen API.

🌐 What is Microsoft Graph API?

Microsoft Graph is the gateway to data and intelligence in Microsoft 365. It's a unified API endpoint (https://graph.microsoft.com) that allows you to access data across various Microsoft 365 services. Think of it as a single, consistent API that lets you tap into the vast amount of information generated by your organization's use of Microsoft products.

With Microsoft Graph API, you can access:

• Users, groups, and roles from Azure Active Directory (Azure AD).
• Emails, contacts, and calendar events from Outlook.
• Teams messages, channels, and team structures.
• Files stored in OneDrive and SharePoint.
• Usage reports for various Microsoft 365 services.

The Graph API is a REST API, which means you interact with it using standard HTTP requests (GET, POST, PUT, DELETE). For example, to get a list of all users in your organization, you'd send an HTTP GET request to https://graph.microsoft.com/v1.0/users.

🔧 What You’ll Build

You will build a custom data connector, which is essentially a .mez file. This file contains the M language code that instructs Power BI on how to:

• Authenticate securely with Azure AD using OAuth 2.0.
• Call the Microsoft Graph API to request specific data (e.g., a list of users).
• Return the retrieved data in a structured format that Power BI can understand and use for reporting.

✅ Prerequisites

Before diving into development, ensure you have the following:

• Power Query SDK installed in Visual Studio: This is a Visual Studio extension that provides project templates and tools for building custom connectors. You can install it via the Visual Studio Installer.
• Power BI Desktop: For testing your custom connector.
• Azure AD App Registration: You'll need an Azure AD application registered to handle the authentication flow with Microsoft Graph.
• Basic knowledge of M language and Power Query: Familiarity with Power Query's functions and M language syntax will be beneficial.

🧱 Step-by-Step Guide

🔹 Step 1: Set Up Azure AD App for Microsoft Graph

This Azure AD application will enable your custom connector to authenticate with Microsoft Graph.

• Go to the Azure Portal.
• Navigate to Azure Active Directory > App registrations.
• Click New Registration.
• For Name: Enter PowerBI-MG-Connector (or a descriptive name).
• For Supported account types: Choose the appropriate option for your organization.
• For Redirect URI: Select "Web" and enter the exact URI: https://oauth.powerbi.com/views/oauthredirect.html. This is a critical URI used by Power BI for OAuth redirection.
• Click Register.
• Once created, from the "Overview" blade of your app registration, save the Application (client) ID. You'll need this in your connector code.
• Go to Certificates & Secrets:
  • Click New client secret.
  • Provide a description and choose an expiration.
  • Immediately save the "Value" of the secret. This value is only shown once! You'll need this for authentication.
• Under API Permissions:
  • Click Add a permission.
  • Select Microsoft Graph.
  • Choose the necessary Delegated permissions based on the data you want to access (e.g., User.Read, Mail.Read, Group.Read.All).
  • Click Add permissions.
  • Important: Click "Grant Admin Consent for [Your Organization]". This allows your application to access Graph API on behalf of all users in your tenant without individual user consent prompts.

🔹 Step 2: Create the Connector Project

Now, let's set up the project in Visual Studio using the Power Query SDK.

• Open Visual Studio.
• Go to File > New > Project....
• Search for "Data Connector" and select the Data Connector Project template.
• Name your project: MicrosoftGraphConnector.
• Click Create.

Your project will be created with several files. The most important ones for our purpose are:

• MicrosoftGraphConnector.m: This is your primary M language file where the connector logic resides.
• MicrosoftGraphConnector.query.pq: A test query file used to test your connector within Visual Studio.
• MicrosoftGraphConnector.pq: Contains metadata about your connector.

🔹 Step 3: Write the Connector Code (.m File)

Open the MicrosoftGraphConnector.m file. This is where you'll define the functions that Power BI will call to get data.

Basic Connector to Get Users:

Replace the existing content in MicrosoftGraphConnector.m with this simplified structure. This shows how to get an access token and then use it to call the Graph API.

// This defines the data source kind and its publishing properties in Power BI
[DataSource.Kind="MicrosoftGraph", Publish="MicrosoftGraph.Publish"]
shared MicrosoftGraph.Contents = () =>
    let
        // Get the access token from the current credential (obtained via OAuth)
        token = TokenMethod(),
        // Call the Microsoft Graph API to get users, passing the bearer token in headers
        users = Json.Document(Web.Contents("https://graph.microsoft.com/v1.0/users", [
            Headers = [Authorization="Bearer " & token]
        ]))
    in
        users; // Return the parsed JSON data (list of users)

// Helper function to extract the access token from the credential
TokenMethod = () =>
    let
        // Extension.CurrentCredential() gets the credential object from Power BI's OAuth flow
        tokenResponse = Extension.CurrentCredential()[access_token]
    in
        tokenResponse; // Return just the access token string

🔹 Step 4: Authentication Handling

This is the most complex part of the connector, defining how Power BI authenticates with Azure AD using OAuth 2.0. This code should also be placed in your MicrosoftGraphConnector.m file, usually at the top or in a dedicated section.

// Define the authentication method for the MicrosoftGraph data source kind
MicrosoftGraph = [
    Authentication = [
        OAuth = [
            StartLogin=StartLogin, // Function to initiate the OAuth login flow
            FinishLogin=FinishLogin, // Function to handle the OAuth callback and get tokens
            Refresh=Refresh // Function to refresh the access token when it expires
        ]
    ],
    Label = "Microsoft Graph Connector" // Label displayed in Power BI Desktop's Get Data dialog
];

// Function to start the OAuth 2.0 Authorization Code flow
StartLogin = (resourceUrl, state, display) =>
    let
        // REPLACE with your Azure AD Application (client) ID
        client_id = "YOUR_CLIENT_ID",
        // This is the exact redirect URI configured in your Azure AD App Registration
        redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html",
        // Construct the authorization URL for Azure AD
        auth_url = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize?" &
            Uri.BuildQueryString([
                client_id = client_id,
                response_type = "code", // Request an authorization code
                redirect_uri = redirect_uri,
                response_mode = "query", // Response parameters in query string
                // Define the scopes (permissions) your connector needs for Microsoft Graph
                scope = "https://graph.microsoft.com/.default offline_access", // .default requests all permissions granted to the app, offline_access for refresh tokens
                state = state // State parameter for security (OAuth standard)
            ])
    in
        [
            LoginUri = auth_url,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

// Function to handle the OAuth 2.0 callback and exchange authorization code for tokens
FinishLogin = (context, callbackUri, state) =>
    let
        // Parse the callback URI to extract the authorization code
        parts = Uri.Parts(callbackUri)[Query],
        code = parts[code],
        // REPLACE with your Azure AD Application (client) ID and Client Secret
        client_id = "YOUR_CLIENT_ID",
        client_secret = "YOUR_CLIENT_SECRET",
        // Token endpoint for Azure AD
        token_url = "https://login.microsoftonline.com/common/oauth2/v2.0/token",
        // Make a POST request to the token endpoint to get access and refresh tokens
        response = Web.Contents(token_url, [
            Content = Text.ToBinary(Uri.BuildQueryString([
                client_id = client_id,
                scope = "https://graph.microsoft.com/.default offline_access",
                code = code,
                redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html",
                grant_type = "authorization_code", // Exchange authorization code for tokens
                client_secret = client_secret
            ])),
            Headers = [#"Content-type"="application/x-www-form-urlencoded"]
        ]),
        // Parse the JSON response containing the tokens
        result = Json.Document(response)
    in
        result; // Return the token object (Power BI SDK expects this structure)

// Function to refresh the access token using the refresh token
Refresh = (resourceUrl, refresh_token) =>
    let
        // REPLACE with your Azure AD Application (client) ID and Client Secret
        client_id = "YOUR_CLIENT_ID",
        client_secret = "YOUR_CLIENT_SECRET",
        // Token endpoint for Azure AD
        token_url = "https://login.microsoftonline.com/common/oauth2/v2.0/token",
        // Make a POST request to refresh the token
        response = Web.Contents(token_url, [
            Content = Text.ToBinary(Uri.BuildQueryString([
                client_id = client_id,
                scope = "https://graph.microsoft.com/.default offline_access",
                refresh_token = refresh_token,
                grant_type = "refresh_token", // Use refresh_token grant type
                client_secret = client_secret
            ])),
            Headers = [#"Content-type"="application/x-www-form-urlencoded"]
        ]),
        // Parse the JSON response containing the new tokens
        result = Json.Document(response)
    in
        result; // Return the new token object

Remember to replace YOUR_CLIENT_ID and YOUR_CLIENT_SECRET with the actual values from your Azure AD App Registration!

🔹 Step 5: Build & Test in Power BI Desktop

Once your M code is ready, you can build your connector and test it directly in Power BI Desktop.

• In Visual Studio, right-click on your MicrosoftGraphConnector project in Solution Explorer and select Build. This will compile your M code and generate a .mez file in your project's `bin\Debug` folder.
• Place the .mez file in the following folder on your computer:

  Documents\Power BI Desktop\Custom Connectors\

  (If the "Custom Connectors" folder doesn't exist, create it.)
• Enable Custom connectors in Power BI Desktop:
  • Open Power BI Desktop.
  • Go to File > Options and settings > Options.
  • In the Options window, navigate to Security > Data Extensions.
  • Check the radio button for "(Not Recommended) Allow extension to load without validation or warning". (This is for development; for production, certified connectors are preferred).
  • Click OK.
• Restart Power BI Desktop for the changes to take effect.
• Now, go to Home > Get Data > Other. You should see "Microsoft Graph Connector" listed.
• Select it, click Connect, and Power BI will prompt you to authenticate using your Azure AD credentials.
• Once authenticated, you should see the data returned from your Graph API call (e.g., a list of users).

🎯 Use Cases of Microsoft Graph in Power BI

The possibilities with Microsoft Graph are vast. Here are some common use cases for bringing this data into Power BI:

Use Case	Example Query URL
User directory reports (e.g., list of all users, their departments, job titles)	https://graph.microsoft.com/v1.0/users
Emails received/sent (e.g., analyze email volume, top senders/receivers for an individual)	https://graph.microsoft.com/v1.0/me/messages
Teams activity (e.g., channel messages, user presence, meeting data)	https://graph.microsoft.com/v1.0/teams
Group membership info (e.g., analyze group structures, user roles in groups)	https://graph.microsoft.com/v1.0/groups/{id}/members
Calendar events and availability (e.g., analyze meeting patterns, resource utilization)	https://graph.microsoft.com/v1.0/me/calendar/events

🧠 Summary

Concept	Meaning
Custom Connector	A custom-built M script that enables Power BI to fetch data from APIs not natively supported, like Microsoft Graph.
OAuth 2.0	A secure industry-standard protocol for authentication, used here with Azure AD App credentials to grant Power BI access to Graph data.
Power Query SDK	A Visual Studio plugin providing templates and tools to simplify the creation and debugging of custom Power BI data connectors (.mez files).
Scopes & Permissions	Specific authorizations defined in your Azure AD App Registration that dictate what parts of Microsoft 365 data your connector is allowed to access via the Graph API.

✅ Bonus Tips

• Always use the v1.0 endpoint for production Graph API calls (https://graph.microsoft.com/v1.0/). The /beta endpoint is for testing new features and can introduce breaking changes.
• For large result sets from Graph API, remember to implement pagination. Graph API responses often include an @odata.nextLink property that points to the next page of results. Your M code will need to loop through these links to fetch all data.
• Consider error handling in your M code to gracefully manage API limits or network issues.

If you'd like, I can give you a ready-to-use sample .m file with pagination logic, or even generate a full Visual Studio project structure with code and documentation.

Would you like that?

Quick Knowledge Check

Q1. What is a Power BI custom connector (.mez file), and when would you build one instead of using Web.Contents() directly in Power Query M?

Show Answer

A custom connector is a packaged M extension that defines a reusable, authenticated data source connection — including OAuth flows, custom UI labels, and navigation tables. Build one instead of using Web.Contents() directly when: (1) you need OAuth 2.0 authentication handled natively by Power BI (not requiring users to manually paste tokens); (2) the connector will be shared across multiple reports or teams; (3) you want gateway support so the connector works in scheduled refresh; (4) you want the API to appear as a named source in the "Get Data" dialog. Raw Web.Contents() with manual token management is fragile and blocks gateway refresh.

Q2. You build a custom connector for Microsoft Graph API. Which Azure AD configuration is required before the connector can authenticate?

• A) No Azure AD configuration is needed — Graph API is public
• B) Register an Azure AD application, configure redirect URI as https://oauth.powerbi.com/views/oauthredirect.html, and grant the required Graph API permissions
• C) Create a service principal and hard-code its client secret in the connector
• D) Power BI automatically registers an app in Azure AD when the connector is installed

Show Answer

B. A custom connector using OAuth 2.0 requires an Azure AD app registration with the Power BI OAuth redirect URI (https://oauth.powerbi.com/views/oauthredirect.html). The app must have the required Microsoft Graph API permissions (e.g., User.Read, Mail.Read) granted and admin-consented. The connector's M code references the Client ID from this registration. Hard-coding secrets (option C) is a security risk and breaks the OAuth flow's purpose.

Q3. Where must a .mez connector file be placed for Power BI Desktop to load it, and what additional step is required in Desktop settings?

Show Answer

Place the .mez file in DocumentsPower BI DesktopCustom Connectors. Then in Power BI Desktop, go to File → Options → Security → Data Extensions and change the setting to "Allow any extension to load without validation or warning." Without this setting, Desktop rejects all unsigned custom connectors. For production deployment on a gateway, install the .mez in the gateway's custom connectors folder and restart the gateway service.

5 Things to Remember

• Custom connectors package reusable, authenticated data sources — OAuth flow, navigation tables, and UI labels are all defined in M. Shareable across reports without re-entering credentials.
• Azure AD app registration required for OAuth connectors — register the app, add the Power BI redirect URI, grant Graph API permissions, and reference the Client ID in the connector.
• .mez file goes in DocumentsPower BI DesktopCustom Connectors — Desktop must be set to allow unsigned extensions (Options → Security).
• Gateway deployment for scheduled refresh — copy the .mez to the gateway server's custom connectors folder and restart the gateway service to enable cloud refresh with the custom connector.
• Pagination is your responsibility — Graph API (and most REST APIs) paginate results. Use List.Generate() or recursive M functions to retrieve all pages before loading into Power BI.

📚 Resources

• Power BI Advanced Technology Topic on RR Skillverse →
• Power BI Skill Cards — Visual Cheat Sheets →
• Power Query Custom Connector SDK — Microsoft Learn →