Forum Discussion

jos_ger's avatar
jos_ger
New Member
2 years ago

API call runs twice

Hi all!

 

I'm trying to create a Power Query that takes variables from an Excel Table and runs an API POST call.

 

I get it to work, BUT for some reason, it runs the call twice each time. Since it's a POST call, this is not tolerable, as duplicate records are not acceptable. 

 

I've been troubleshooting several hours but now I'm stuck. Anyone who know what I can do? I would deeply appreciate any ideas.

 

The code:

 

let
url = "XXX", // my URL
xml = "XXX", // my XML
headers = [#"Content-Type"="application/xml"],
options = [Headers=headers, Content=Text.ToBinary(xml)],
Source = Web.Contents(url, options)
in Source

 

 

 

 

What I've tried:

  • Deselect “Allow data preview to download in the background” (as mentioned as a possible solution elsewhere).
  • Use Table.Buffer as in:

 

headers = [#"Content-Type"="application/xml"],
options = [Headers=headers, Content=Text.ToBinary(xml)],
Source = Web.Contents(url, options),
XmlTable = Xml.Tables(Source),
BufferedTable = Table.Buffer(XmlTable)
in BufferedTable​

 

 

 

 

 

Need Help

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    Hi jos_ger ,

     

    Are you referencing this query at all? If you reference a query, in almost all cases both queries will actually hit the source.

    Also, your table buffer isn't doing anything here except filling up memory then getting dumped out again. Object buffers aren't persistent in Power Query in that way.

     

    Pete

  • ImkeF's avatar
    ImkeF
    Community Champion
    2 years ago

    Hi jos_ger ,
    Power Query is not suitable for write back, as it will by default execute queries multiple times: The first execution is to retrieve metadata in order to create a query execution plan. After that it will execute the execution plan.
    There is no way you can suppress that.

    Easiest way would probably be to use a tool like Power Automate.