cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
alietzau Frequent Visitor
Frequent Visitor

Running Graph API queries

To start, the queries that I would like to run are: 

 

I have tried many different ways to try and get the information from this query. I know it works in Graph Explorer, I tried to run it using Postman (Instructions from Microsoft) and the query doesn't work with the AAD App I made. I've tried to use the Microsoft Graph Security connector, but I can only use certain queries (e.g. https://graph.microsoft.com/v1.0/me/licenseDetails).  There's another thread where they discuss how to pull data from Graph API into powerBI, but the guide was published a year ago and many of the links in it are broken. I've tried to talk to different people about how to pull in the data from Graph API but I haven't heard anything super helpful. They told me to use generic REST API calls to run the query, but I don't know how to handle access tokens with PowerBI.

 

There's a lot that I've tried here and I'm surprised that this isn't easier since Graph API is so useful. If I can get the help I need to run just one query, I'm sure I'll be able to use the same process for the other queries and I'll be able to produce a project that is useful for our entire community.

2 ACCEPTED SOLUTIONS

Accepted Solutions
ImkeF
MVP

Re: Running Graph API queries

I would also prefer that this would be easier.

Currently you have to:

1) use a custom connector and

2) create an app in the azure portal that handles the authentication for the connector

 

With my connector, I'm able to see the license details. Unfortunately I cannot add the permissions for the subcriptions to the app, as that's a preview feature currently. 

 

Please let me know if you want to proceed just for the license details and I can provide further help on how to implement the connector. Am I getting it right that you have registered you app successfully already?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

ImkeF
MVP

Re: Running Graph API queries

Hi @alietzau ,

great - thanks for the tip - that worked for me as well! Actually, didn't have to clear my other permissions fortunately.

 

You can try my connector. The enclosed zip includes all files necessary. You have to perform the following steps:

1) Unzip it

2) edit client_id: replace content by the client id of your app

3) edit client_secret: replace content by the client secret of you app

4) zip all files back and rename the zip to "MyGraphB.mez"

5) Copy that file into your Power BI Desktop-folder: C:\Users\<YourUserName>\Documents\Power BI Desktop\Custom Connectors

 

Now, when you type in "mygr" for example, these 2 items should show up:

 

image.png

Choose the latter ("MyGraphB2.WebContents") and paste the URL of your choice.

If interested, I've described the Web.Contents-addition here: https://www.thebiccountant.com/2019/04/17/graph-api-custom-connector-power-bi/

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

10 REPLIES 10
Community Support Team
Community Support Team

Re: Running Graph API queries

Hi @alietzau ,

It seems that you want to use the Graph API  as the data source in power bi desktop.

If that is the case, please refer to this similar thread which has been solved firstly.

If you need other help, please feel free to ask.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
alietzau Frequent Visitor
Frequent Visitor

Re: Running Graph API queries

@v-piga-msft The post that you linked is the same as the one that I refer to in my original post. The guide that they link to is not a step by step guide and it is hard to follow what they are trying to do and where the powerquery goes. Many of the links for supporting material in that same guide are broken, it's hard to investigate this issue. There are also users at the end of that post that mention that they are still having issues after looking into this for months. 

 

I think that Graph API is a powerful resource to have and it's a shame that the PowerBI community doesn't have a clear way to pull in the information from it. I think if we resolve this, we can help the community by creating powerful dashboards without the need for third party tools. 

ImkeF
MVP

Re: Running Graph API queries

I would also prefer that this would be easier.

Currently you have to:

1) use a custom connector and

2) create an app in the azure portal that handles the authentication for the connector

 

With my connector, I'm able to see the license details. Unfortunately I cannot add the permissions for the subcriptions to the app, as that's a preview feature currently. 

 

Please let me know if you want to proceed just for the license details and I can provide further help on how to implement the connector. Am I getting it right that you have registered you app successfully already?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

alietzau Frequent Visitor
Frequent Visitor

Re: Running Graph API queries

@ImkeF I was just able to get the queries that I listed to work, using the permission of "Directory.Read.All" and making sure that no other permissions were added to my App. I'm listing the results of the queries below for reference.

 

Now all I need to do is build the powerquery to run the Graph API query. I took another look at the guide that mention in my original post and it's still hard to follow. Do you have a good guide to building this custom connector? I think this is the final road block stoping me from using the data. 

ImkeF
MVP

Re: Running Graph API queries

Hi @alietzau ,

great - thanks for the tip - that worked for me as well! Actually, didn't have to clear my other permissions fortunately.

 

You can try my connector. The enclosed zip includes all files necessary. You have to perform the following steps:

1) Unzip it

2) edit client_id: replace content by the client id of your app

3) edit client_secret: replace content by the client secret of you app

4) zip all files back and rename the zip to "MyGraphB.mez"

5) Copy that file into your Power BI Desktop-folder: C:\Users\<YourUserName>\Documents\Power BI Desktop\Custom Connectors

 

Now, when you type in "mygr" for example, these 2 items should show up:

 

image.png

Choose the latter ("MyGraphB2.WebContents") and paste the URL of your choice.

If interested, I've described the Web.Contents-addition here: https://www.thebiccountant.com/2019/04/17/graph-api-custom-connector-power-bi/

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

alietzau Frequent Visitor
Frequent Visitor

Re: Running Graph API queries

@ImkeF Thank you! I think this gets me super close but I'm still hitting a roadblock. I've updated the client_id and secret file and packaged it just like you said. However, when I try to use the connector to run my query, I get this error. 

Powerbi Error.png

 

Do you know why it's not finding the client_id? I'm sure that the name matches the file in the .mez file. I'm not familiar enough with custom connectors to determine why it would be having this issue.

 

Also, thank you for your help with all of this. 

ImkeF
MVP

Re: Running Graph API queries

The error message means that the file is not found, so the actual content hasn't been checked yet.

Please check that when saving the file, no extension has been added to the file.

 

If nothing else helps, you can only integrate the client_id as a direct variable in the connector code instead of referencing it from an external file.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




alietzau Frequent Visitor
Frequent Visitor

Re: Running Graph API queries

@ImkeF That work! Thank you so much! This is great, I'm glad that I'm able to start working on the report now that I can bring the data in. It's a shame that I can't have the queries update in PowerBI Pro, but with how useful this information is, I'll take that hit. 

 

Again, thank you so much for your help.

alietzau Frequent Visitor
Frequent Visitor

Re: Running Graph API queries

@ImkeF , I hope you don't mind if I keep pestering you, but I've tried to run queries that involve more than 100 returns, so it gives me a @odata.nextlink value. Do you know how I can get PowerBI to retreave the next 100 values? I haven't seen any discussion on this with PowerBI in mind.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,288)