Forum Discussion
Power Query is sending multiple requests when using Web.Contents
Hi,
We have a situation where a POST API call is followed by a GET call. We are using Web.Contents to create a POST API call to a GRAPH API. In the next step, we extract the ID from the response to make a GET API call to fetch records. However, we have noticed that Power Query is making multiple POST requests. We have disabled data preview in the background and set parallel loading to 1. Kindly help in resolving the issue. Our focus is to make a single POST call. Below is an example of the Power Query script:
let
access_token = "xxxx",
GetId = () =>
let
// Define the URL for the POST request
url = "https://posturl1", // GRAPH API
// Define the headers for the POST request
headers = [
#"Content-Type" = "application/json",
#"Authorization" = "Bearer " & access_token
],
currTime = Text.From(DateTime.LocalNow()),
// Define the body of the POST request
body = "{
""displayName"": """ & currTime & """,
""filterStartDateTime"": ""2025-02-24T07:02:00"",
""filterEndDateTime"": """ & currTime & """
}",
// Convert the body to binary
binaryBody = Text.ToBinary(body),
// Make the POST request
response = Web.Contents(url, [
Headers = headers,
Content = binaryBody
]),
// Parse the JSON response
jsonResponse = Json.Document(response),
// Extract the ID field from the JSON response
extractedId = if Record.HasFields(jsonResponse, "id") then jsonResponse[id] else null
in
extractedId,
ExtractedIdResult = try GetId() otherwise null,
FetchRecords = () =>
OData.Feed(
"https://geturl2/" & ExtractedIdResult & "/records", // GRAPHAPI
[Authorization = "Bearer " & access_token],
[ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0"]
),
Records = if ExtractedIdResult <> null then
let
records = Function.InvokeAfter(FetchRecords, #duration(0,0,5,0)) // 5minute delay
in
records
else
#table({}, {}),
Hi bansalpreeti88,
- Thank you for the response. Power query’s evaluation engine does not guarantee that any step (especially one that makes a Web.Contents call) will run only once. Even with background previews disabled and parallel loading set to 1, power query may re evaluate steps for metadata, schema validation or during applied steps navigation. This includes power bi desktop as well.
- To ensure the POST call is made only once, it is necessary to avoid wrapping the Web.Contents call in a function that can be invoked multiple times. And immediately cache the POST result in a let block so it’s evaluated once and reused.
Here is the optimized version of the Power Query code:
let
access_token = "xxxx",// Step 1: Make POST call and cache result
PostResponse =
let
url = "https://posturl1",
headers = [
#"Content-Type" = "application/json",
#"Authorization" = "Bearer " & access_token
],
currTime = Text.From(DateTime.LocalNow()),
body = "{
""displayName"": """ & currTime & """,
""filterStartDateTime"": ""2025-02-24T07:02:00"",
""filterEndDateTime"": """ & currTime & """
}",
binaryBody = Text.ToBinary(body),
response = Web.Contents(url, [
Headers = headers,
Content = binaryBody
]),
jsonResponse = Json.Document(response),
extractedId = if Record.HasFields(jsonResponse, "id") then jsonResponse[id] else null
in
extractedId,// Step 2: Use cached ID in GET call
Records =
if PostResponse <> null then
let
getUrl = "https://geturl2/" & PostResponse & "/records",
result = OData.Feed(getUrl,
[Authorization = "Bearer " & access_token],
[ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0"]
)
in
result
else
#table({}, {})
in
RecordsAlso check that all preview related settings are disabled, go to File --> Options and settings --> Options --> Global --> Data Load and also check under Current File --> Data Load
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
6 Replies
- lbendlin
Super User
Power Query gives no guarantee whatsoever that it will run queries only once. You are not supposed to write anything back into the data source in your Power Query code. It is technically possible but it can lead to duplicates being written.
We have disabled data preview in the background and set parallel loading to 1.That setting only applies to Desktop and is ignored in the service
You need to debounce this yourself.
- v-achippa
Community Support
Hi bansalpreeti88,
Thank you for reaching out to Microsoft Fabric Community.
Thank you lbendlin for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
- bansalpreeti88New Member
Thank you lbendlin for providing quick response and your time.
v-achippa I am still looking for solution of this issue. As following setting doesn't work for me even in Power BI desktop application. It will be great if we can get further assistance.
We have disabled data preview in the background and set parallel loading to 1.
- v-achippa
Community Support
Hi bansalpreeti88,
- Thank you for the response. Power query’s evaluation engine does not guarantee that any step (especially one that makes a Web.Contents call) will run only once. Even with background previews disabled and parallel loading set to 1, power query may re evaluate steps for metadata, schema validation or during applied steps navigation. This includes power bi desktop as well.
- To ensure the POST call is made only once, it is necessary to avoid wrapping the Web.Contents call in a function that can be invoked multiple times. And immediately cache the POST result in a let block so it’s evaluated once and reused.
Here is the optimized version of the Power Query code:
let
access_token = "xxxx",// Step 1: Make POST call and cache result
PostResponse =
let
url = "https://posturl1",
headers = [
#"Content-Type" = "application/json",
#"Authorization" = "Bearer " & access_token
],
currTime = Text.From(DateTime.LocalNow()),
body = "{
""displayName"": """ & currTime & """,
""filterStartDateTime"": ""2025-02-24T07:02:00"",
""filterEndDateTime"": """ & currTime & """
}",
binaryBody = Text.ToBinary(body),
response = Web.Contents(url, [
Headers = headers,
Content = binaryBody
]),
jsonResponse = Json.Document(response),
extractedId = if Record.HasFields(jsonResponse, "id") then jsonResponse[id] else null
in
extractedId,// Step 2: Use cached ID in GET call
Records =
if PostResponse <> null then
let
getUrl = "https://geturl2/" & PostResponse & "/records",
result = OData.Feed(getUrl,
[Authorization = "Bearer " & access_token],
[ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0"]
)
in
result
else
#table({}, {})
in
RecordsAlso check that all preview related settings are disabled, go to File --> Options and settings --> Options --> Global --> Data Load and also check under Current File --> Data Load
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
- v-achippa
Community Support
Hi @bansalpreeti88,
We wanted to kindly follow up to check if the solution I have provided, resolved your issue.
If my response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa