Creating a Custom Data Connector for Microsoft Graph API in Power BI
Creating a Custom Data Connector for the Microsoft Graph API in Power BI
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
MicrosoftGraphConnectorproject in Solution Explorer and select Build. This will compile your M code and generate a.mezfile in your project's `bin\Debug` folder. - Place the
.mezfile in the following folder on your computer:(If the "Custom Connectors" folder doesn't exist, create it.)Documents\Power BI Desktop\Custom Connectors\
- 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.0endpoint for production Graph API calls (https://graph.microsoft.com/v1.0/). The/betaendpoint 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.nextLinkproperty 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?
Comments
Post a Comment