Display Office 365 data using MS Graph API in Power BI

Learn how you can consume MS Graph API data in Power BI

Objective:

In this article, I will show how you can display Office 365 data of your organization in Power BI report using MS Graph API.

I will show how you can fetch your organization’s users with MS Graph API, and quickly create a report using Power BI Desktop. Once you learn to use MS Graph API in Power BI, you can explore more options to create some interesting reports on your own.

Background:

This article assumes you know something about MS Graph API and Power BI.

What is MS Graph API?

Excerpt from https://developer.microsoft.com/en-us/graph/docs/concepts/overview

You can use the Microsoft Graph API to interact with the data of millions of users in the Microsoft cloud. Use Microsoft Graph to build apps for organizations and consumers that connect to a wealth of resources, relationships, and intelligence, all through a single endpoint: https://graph.microsoft.com

For more on MS Graph API, please go to https://developer.microsoft.com/en-us/graph

What is Power BI?

Excerpt from https://powerbi.microsoft.com/en-us/

Power BI is a suite of business analytics tools that deliver insights throughout your organization. Connect to hundreds of data sources, simplify data prep, and drive ad hoc analysis.”

For more on Power BI, please go to https://powerbi.microsoft.com/en-us/

There is no coding involved to follow steps of this article. However, if you want to follow along the steps, then you will need things mentioned in “Prerequisites” section. If you don’t want to follow along and just want to read, you can jump to “Getting Started” section.

Prerequisites:

  • Office 365 developer account

You may have access to Office 365 thru your employer/organization account. However, it is strongly advised that you don’t use your live/organization account to follow steps of this article. Instead, use Office 365 developer account.

Read my blog on how to get Office 365 developer account for 1 year free here.

  • Power BI Desktop

You will need Power BI Desktop to create the report which will consume MS Graph API data.

Power BI Desktop can be downloaded free from this link:

https://www.microsoft.com/en-us/download/details.aspx?id=45331

Power BI Desktop getting started guide is here:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-getting-started/

 

Getting Started:

I assume you have Office 365 developer account and you have installed Power BI Desktop on your PC.

Let’s get started.

Open Power BI Desktop.

PowerBI1

Once open, you will see it’s welcome screen:

PowerBI2

Click on “Get Data” just below Power BI Desktop header on top left.

Alternatively, if you close this welcome screen, then you will also see “Get Data” icon at top left:

PowerBI3

Once click on “Get Data”, you will be presented with a “Get Data” dialog. Click on “Other” in left-middle section:

Get data

Once you click on “Other” link, on the right side of dialog you will see some options. Select “OData Feed”, then click on “Connect” button:

Odata Feed

You will see a dialog to enter OData feed URL:

Odata Feed blank

Why we choose this option?

MS Graph API is based on open web standards and it supports OData V4, and MS Graph API accepts and returns data in JSON format, making it easy to integrate with other applications and technologies.

We want to access all the users of an organization. The MS Graph API endpoint https://graph.microsoft.com/v1.0/users returns all users of an organization.

Enter https://graph.microsoft.com/v1.0/users in the textbox under URL and click OK:

Odata Feed2

Once you click on OK, you will see a dialog where you can specify your credentials to connect to MS Graph API.

Odata Feed3

Click on “Organizational account”, then click on “Sing in” button:

Odata Feed4

You will see Office 365 log in dialog:

O365 login

Don’t use your organization/live account to sign in. Use your Office 365 developer account to sign in.

Once you login successfully that dialog will close, and your status on OData feed dialog will change to singed in:

O365 login2

Click on Connect button to continue.

Once Power BI fetches the users using MS Graph API, it will show you in a dialog. For testing purpose, I have created some users using my developer account. I suggest you also create some test users in your Office 365 developer account using Office 365 Portal.

I have masked some sensitive data from my popup:

user data

You should also see your organization’s users here. Click on “Load” to continue.

You will see some messages in a popup while Power BI loads your data using MS Graph API:

loading

Once this dialog closes, on the right side you will see the data table under “Fields” section:

fields

What has happened here?

Power BI Desktop has received the JSON data from MS Graph API in response to the call to https://graph.microsoft.com/v1.0/users endpoint, and converted it to a data table for you. What you see under the Fields -> Query1 section is the list of all properties it got from MS Graph API.

Now we have the data we want from MS Graph API loaded into Power BI. Let’s create a report using it.

Click on “Table” icon under Visualizations on right side of screen:

table

You will see your screen will be loaded with an empty table:

empty table

Now we should select which fields we want to show in table. Under Fields section on right, select displayName, jobTitle and officeLocation fields in that order:

fields selection

As and when you select the fields, you will see the table being loaded with data:

table with data

Hurray, you have created your first Power BI report using MS Graph API!

What’s next?

The purpose of this article was only to show you how MS Graph API data can be consumed in Power BI, which I have shown above. Similarly, you can try by yourself creating some other reports/visualizations and calling some more MS Graph API endpoints.

If you want to share the report, you can do it using the Publish feature of Power BI:

publish

But this requires a Power BI account and it’s out of scope for this article. I suggest trying it on your own.

Meanwhile if you want to read more of my articles on MS Graph API, please go to https://nilesh.live/blogs/msgraph

One thought on “Display Office 365 data using MS Graph API in Power BI”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s