Display Office 365 Data Using MS Graph API In Power BI

Nilesh Shah

9y36.9k7100Article

Power BIMS Graph APIOffice 365 Data

7 Comments

Join the conversation! Your thoughts help the community grow.

Shamp8y ago
This is not working anymore Please refer : https://community.powerbi.com/t5/Issues/Error-getting-OData-from-Microsoft-Graph-Access-to-the-resource/idi-p/430087
- Nilesh Shah8y ago
  Thanks for the update. Currently, I am not aware of a fix. An issue raised with Microsoft. Will keep posted when it is answered.
- Nilesh Shah8y ago
  Microsoft disabled the OData feed for Excel and Power BI. This will no longer work. I will update the article accordingly.
Bob Palmer8y ago
Is the developer account required to make this connection, or should I be able to make this connection with my organizational account?
- Nilesh Shah8y ago
  Developer account is not needed if you already have Office 365 subscription thru your organization. But I strongly suggest to create and use developer account for all your testing/trial and errors for learning.
Benjamin Swift8y ago
Hi there. Thanks a lot for this info, which has helped me to finally connect to our AAD users from Power BI Desktop. However, any idea why the users dataset which can load via OData feed does not include ALL user fields/attributes? For example, the key field I need is the Alternate email, which for some reason can be added/updated in Azure AD but is nowhere to be seen on O365 Admin - although for some reason it does appear on the csv user export from O365 (which isn't useful for me as I need a dynamic user list in Power BI Desktop which automatically updates at refresh time). Any idea how I can access all the user attributes from Power BI Desktop via the Graph call? Thanks. Ben Swift
- Nilesh Shah8y ago
  By default MS Graph API only give you some of the fields it considers important, but you can include the $select optional parameter in the API endpoint for another fields, read my article for more info on $select- http://www.c-sharpcorner.com/article/using-optional-query-parameters-with-microsoft-graph-api-part-two/

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.

Office Development

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.

Office Development

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.

Office Development

You will see a dialog to enter OData feed URL.

Office Development

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,

Office Development

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.

Office Development

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.

Office Development

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.

Office Development

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.

Office Development

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.

Office Development

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.

Office Development

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