API call runs twice

[jos_ger]

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​

[ImkeF]

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.

[BA_Pete]

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