Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alietzau
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

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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/

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

12 REPLIES 12
JanBakker
New Member

alietzau
Frequent Visitor

@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.

Yes, that's called paging or pagination. 

A good post is this: https://datachant.com/2016/06/27/cursor-based-pagination-power-query/ 

But your favourite search engine will return many other samples as well 😉

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

v-piga-msft
Resident Rockstar
Resident Rockstar

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.

@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. 

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-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. 

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/

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-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. 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thank you for the solution but when integrated Client ID and Client Secret directly in the code, it's not able to find correct app in the azure directory. In MyGraph.pq file, I have updated Client_ID and Client_Secret. Could you please let me know if I have to make those changes in any other file?

 

I did change the Token uri and Authorize uri in mygraph.pq and then I was able to sign in. but now on the next screen I am getting error

DeepaliCyc_0-1655461431939.png

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors