Help With Power Query, Power Automate and Asynchronous using Graph API

[notsopowerbiguy]

Hi,

I was wondering if someone could help (bit of a novice).  I am running a power automate flow to do the following:

The HTTP Request is a call to the Graph API to get a list of Users (the filter is to filter out members only so as not to return guests).

I have used this method for devices or other bits of information I want to get from Graph into Power BI.

The issue on this occasion is that if I run it normally the HTTP response times out before the call to the Graph API is completed.  Does anyone have any tips on how to fix this?

I have read up online about asynchronous (To be honest it is a bit over my head).  When I change the flow it does run but the query in Power BI finishes immediatley and the only data I get into Power BI is the status of the flow (saying it is still running etc.)  I will share the query I am using in Power BI (replacing the endpoint post URL with my URL):

let

    url = "https://your_logic_app_endpoint_post_url",

    body  = "{}",

    Parsed_JSON = Json.Document(body),

    BuildQueryString = Uri.BuildQueryString(Parsed_JSON),

    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(BuildQueryString) ] ))

in

    Source

 

Any help would be greatly appreciated as currently tearing my hair out for 3 days on this.

Thanks

[notsopowerbiguy]

Hi,

I still wasn't able to get this working unfortunately.  Can you definetly use asynchronus with power queries?

Thanks

[notsopowerbiguy]

Thank you for your reply and explanation.  Ah I didn't know you could use this logic - that would make more sense.  I have tried to use the final query you provided but it is giving me "An error occurred in the ‘’ query. Expression.Error: The name 'Task.Await' wasn't recognized. Make sure it's spelled correctly."

I have even tried using task.await and async in the queries as you described but it throws up an error that it doesn't recognise them either, I am probably building it wrong or doing something incorrect.

I am not overly familiar with Power Query so struggling to find the error of the query when googling.

[nitishsh91]

I tried this thing in a different manner too, give it a try and see if it works for you:

You will have to to make sure that the flow is completed before the Power BI query is executed. 

Add a delay action in your Power Automate flow after the "HTTP" action and before the "Compose" action. This will ensure that the flow has time to complete the Graph API call before moving on to the next step.

Another way to fix this is to use the Web.Page function in Power BI, which can wait for the flow to complete before returning the data.

let url = "https://your_logic_app_endpoint_post_url",
Source = Web.Page(Web.Contents(url))
in
Source

You can also try to increase the Timeout property when you are using the Web.Contents function.

let
url = "https://your_logic_app_endpoint_post_url",
Source = Json.Document(Web.Contents(url, [Timeout=120]))
in
Source

I have used a similar kind of setup for one of my long running flow.

[notsopowerbiguy]

Hi,

Thanks for the suggestions - so on trying I updated my query to be:

let

url = "My URL",
body = "{}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Web.Page(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(BuildQueryString) ] ))
in
Source

I receive back an error, maybe I am building it wrong?

Expression.Error: The Web.Contents 'Content' option isn't supported with Web.Page.

On the second point I did try this but because it is doing the call in the logic app to the graph API here: 

If I put a delay in before it just waits before making the call - if I put the delay in after it still times out before it gets to the delay 😭

If I put the delay in Parallel and update the power query Timeout = #duration(0,0,10,0) 

We still run into the timeout issue:  

Thanks

[nitishsh91]

Hi @notsopowerbiguy 

One way to fix this is to use an asynchronous approach to handle the HTTP request.

This ishow you can modify your Power Query to use an asynchronous approach:
Start by creating a new query in Power Query.
In the query, use the Web.ContentsAsync function to make the HTTP request to the Graph API. This function returns a task that can be used to handle the request asynchronously.
let
url = "https://your_logic_app_endpoint_post_url",
body = "{}",
task = Web.ContentsAsync(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )

Use the Task.Wait function to wait for the task to complete, and then use the Task.Result function to extract the response from the task.
Finally, use the Json.Document function to parse the response as JSON and extract the data you need.
You can also use the Task.Await function instead of Task.Wait and Task.Result, which allows you to write the code in a more readable and concise way.
let
url = "https://your_logic_app_endpoint_post_url",
body = "{}",
response = Task.Await(Web.ContentsAsync(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
data = Json.Document(response)

By using an asynchronous approach, your query will wait for the Graph API call to complete before attempting to extract the data, which should avoid the timeout issue.

Please let me know if this helps or if you have any other questions.