Display Office 365 Data Using MS Graph API 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 here -

“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. Power BI Desktop getting started guide is here. 

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.



Once opened, you will see it’s welcome screen.



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

Alternatively, if you close this welcome screen, you will also see “Get Data” icon at the top-left.



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



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.



You will see a dialog to enter OData feed URL.

Why do 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 the 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,



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



Click on “Organizational account”, then click on “Sign in” button.



You will see Office 365 log in dialogue.



Don’t use your organization/live account to sign in. Use your Office 365 developer account instead. Once you are logged in successfully, that dialogue will close and your status on OData feed dialog will change to signed in.

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 purposes, 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.



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.



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



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.



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



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



As and when you select the fields, you will see the table being loaded 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.



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 http://www.c-sharpcorner.com/members/nilesh-shah9/articles