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:
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
- Visual Studio 2017 or higher version
- Visual Studio Power Query SDK
- Power BI Desktop
- Power BI Custom Connector Setup
- Global Admin privileges on Azure Active Directory
- Get source code from here
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