Forum Discussion

bansalpreeti88's avatar
bansalpreeti88
New Member
1 year ago
Solved

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

 

  • v-achippa's avatar
    v-achippa
    1 year ago

    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

6 Replies

  • 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's avatar
    v-achippa
    Icon for Community Support rankCommunity 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

  • 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's avatar
      v-achippa
      Icon for Community Support rankCommunity 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
      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's avatar
        v-achippa
        Icon for Community Support rankCommunity 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