Microsoft Graph Power BI Power Platform

Microsoft Graph API Custom Connector for Power BI

Hello there, developers! Today, we will build Microsoft Graph API custom connector for Power BI by using M Language. For those who have never heard M Language before, here is a quick ramp-up video:

Deep Dive into the M Language

In this scenario, our purpose is to build a custom connector to get Office 365 data via Graph API and use it as a data model in Power BI. So, we will be focusing on getting Microsoft Teams’ all provisioned team list with members through the connector.

Lets start with pre-requisites

Create AAD Registered Graph Application

Login Azure Portal and go to Azure Active Directory on the left side of the menu, then select App registrations and create New registration.

Give a name to your app, select preferred supported account type for your organization.
Make sure to select Public client/native (mobile/desktop) for Redirect URI and place the following redirect URL: “https://oauth.powerbi.com/views/oauthredirect.html“, then click register.

Once our app is registered, go to API Permissions > Add a permission and choose Microsoft Graph.

Select below permissions below for both Delegated Permissions and Application Permissions:

User.Read
User.Read.All
User.ReadWrite.All
Group.Read.All
Group.ReadWrite.All

Then, click Grant Admin Content for -your organization-.

Finally, go to Overview from the left side of the menu and copy Application (client) ID to a notepad. We will need this id later.

Build the connector

After successfully registering the app in Azure Active Directory, we are ready to create a Data Connector Project in Visual Studio.

  • Add Registered App Client ID in MyGraphConnector project

    As you already copied your Application (Client) ID to a notepad, go ahead and save it as client_id.txt file.
    Add your existing client_id.txt file in MyGraphConnector project, rename and remove txt extension from the file name.
    Finally, click on client_id and change the Build Action setting from Properties as Compile.

  • Configure OAuth for Graph API in MyGraphConnector.pq

    Let’s set the variables first:
section MyGraphConnector;
// TODO: make sure to set AAD client ID value in the client_id file
client_id = Text.FromBinary(Extension.Contents("client_id")); 
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
token_uri = "https://login.microsoftonline.com/organizations/oauth2/v2.0/token";
authorize_uri = "https://login.microsoftonline.com/organizations/oauth2/v2.0/authorize";
logout_uri = "https://login.microsoftonline.com/logout.srf";
windowWidth = 720;
windowHeight = 1024;

Define Registered App scope:

scope_prefix = "https://graph.microsoft.com/";
scopes = {
   
    "Group.Read.All",
    "Group.ReadWrite.All",
    "User.Read",
    "User.Read.All",
    "User.ReadWrite.All"
  
};
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
    let
        prefix = Value.IfNull(scopePrefix, ""),
        addPrefix = List.Transform(scopes, each prefix & _),
        asText = Text.Combine(addPrefix, " ")
    in
        asText;

OAuth Implementation:

// StartLogin builds a record containing the information needed for the client
StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
            client_id = client_id,  
            redirect_uri = redirect_uri,
            state = state,
            scope = "offline_access " & GetScopeString(scopes, scope_prefix),
            response_type = "code",
            response_mode = "query",
            login = "login"
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];
// FinishLogin is called when the OAuth flow reaches the specified redirect_uri. 
FinishLogin = (context, callbackUri, state) =>
    let
        // parse the full callbackUri, and extract the Query string
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;
// Called when the access_token has expired, and a refresh_token is available.
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;
// code:       Is the actual code (authorization_code or refresh_token) to send to the service.
TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            client_id = client_id,
            scope = "offline_access " & GetScopeString(scopes, scope_prefix),
            grant_type = grantType,
            redirect_uri = redirect_uri
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),
        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Graph API Connector Implementation:

//Graph API URL to get all provisioned team list with members 
url= "https://graph.microsoft.com/beta/groups?$expand=members&$filter=resourceProvisioningOptions/Any(x:x eq 'Team')";
//
// Exported function(s)
//
[DataSource.Kind="MyGraphConnector", Publish="MyGraphConnector.UI"]
shared MyGraphConnector.Feed = () =>
    let
        source = OData.Feed(url, null, [ ODataVersion = 4, MoreColumns = true ])
      
      in
        source;
  
//
// Data Source definition
//
MyGraphConnector = [
    TestConnection = (dataSourcePath) => { "MyGraphConnector.Feed" },
    Authentication = [
        OAuth = [
            StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
        ]
    ],
    Label = "My Graph Connector"
];
//
// UI Export definition
//
MyGraphConnector.UI = [
    Beta = true,
    ButtonText = { "MyGraphConnector.Feed", "Connect to Graph" },
    SourceImage = MyGraphConnector.Icons,
    SourceTypeImage = MyGraphConnector.Icons
];
MyGraphConnector.Icons = [
    Icon16 = { Extension.Contents("MyGraphConnector16.png"), Extension.Contents("MyGraphConnector20.png"), Extension.Contents("MyGraphConnector24.png"), Extension.Contents("MyGraphConnector32.png") },
    Icon32 = { Extension.Contents("MyGraphConnector32.png"), Extension.Contents("MyGraphConnector40.png"), Extension.Contents("MyGraphConnector48.png"), Extension.Contents("MyGraphConnector64.png") }
];

  • Update MyGraphConnector.query.pq
MyGraphConnector.Feed()

  • Run the project

Run the application in Debug mode. You should be able to receive below app:

Select your Credential Type as OAuth2, and paste your accessToken for the login url https://login.microsoftonline.com/organizations/oauth2/v2.0/token. (To learn how to get accessToken: https://docs.microsoft.com/en-us/graph/auth-v2-user)

Click Login, then type your credentials. After the successful login, click on Set Credential. You can go to Credentials tab to check if your credentials set properly.

Finally, press CTRL + F5 to get the data with connector.

  • Create MyGraphConnector.mez package

    If your app is successfully getting the data as above, let’s run the project in Release mode to create release package.

Copy your MyGraphConnector.mez from
[Your Project Folder]\MyGraphConnector\MyGraphConnector\bin\Release.

Paste it under
C:\Users\[Your User Name]\Documents\Power BI Desktop\Custom Connectors.

Test your connector in Power BI Desktop

Open your Power BI Desktop and select Get Data, click More. MyGraphConnector.Feed (Beta) should appear under Online Services. Select and connect.

Sign in with your credentials and connect.

All provisioned teams list with members should appear in Power BI as data table.


Below is the list of all properties we are able to get with the following Graph API:

graph.microsoft.com/beta/groups?$expand=members&$filter=resourceProvisioningOptions/Any(x:x eq ‘Team’)


You can get the source code from here: https://github.com/aycabas/Graph-API-Custom-Connector. I hope you find the post useful. Let me know about your experience, share your questions and comments below.

Cheers!

Leave a Reply

%d bloggers like this: