Power Query is sending multiple requests when using Web.Contents

[bansalpreeti88]

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({}, {}),

[bansalpreeti88]

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 · Accepted solution]

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
Records

Also 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]

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

[v-achippa]

Hi @bansalpreeti88,

As we haven’t heard back from you, 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

[v-achippa]

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

[lbendlin]

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.