Hi all!
While I realize it's predominantly a PowerBI board, I'm working in Excel 365 environment. Still, I expect it to be an M / Power Query issue. Big thanks to @ImkeF and her Thebiccountant blog for describing HTTP POST request via Power Query - super useful for Windows desktop environment (for posting data from Excel + VBA arrays etc).
With that being said - whenever I refresh my query TWO identical consecutive posts are sent to the endpoint. There are also other similar threads to be found (dating back to 2017) about excess HTTP requests being sent out by PQ, but haven't really seen a solution.
The query below contacts a SMS message sending service which creates two duplicate queries/records in server for each data refresh (though the goal is to send out a single SMS). I have simplified it down as much as I can to see if it helps, but sadly not (in production it should fetch phone number and text contents from other query, but tested hardcoding this to limit complexity). Also removed M steps to convert the result (HTTP POST response) into a table. There are also some things worth considering:
1) When running the same HTTP POST through Postman platform, everything works just fine (single record reaches the server - so the endpoint itself should be fine)
2) I'm sending HTTP POST requests regularly to 3 different endpoints - independent from each other, but have more or less copied the same M syntax to each. One is an even simpler service (send company ID in, get company contacts back) - this works fine. The other is more complex (send transportation assignments to logistics system) - duplicate records are similarly created in server (I have overcome it by other means, even though the original issue remains - though again, no issues over Postman). So, the same problem also occurs with other endpoints, but apparently not with every last one.
3) I have also created an alternative testing account for the SMS service below. The difference there being that all phone numbers are blacklisted and service is essentially non-functional. With that said, requests still work (don't provide an error message) and give an response which I can also monitor from the server side. For that user / API key, there are NO duplicates, each refresh creates a single record.
4) (edit) I have also tested a different, but technically similar service provider for SMS messaging and experience this - while first data connection refresh posts 2 records to server, the second query without changing anything correctly posts a single record. Duplicates are posted only when something is changed in the query (e.g. phone number or text - which obviously happens in production use) This is NOT what I experience with the connection below as this duplicates every request though noteworthy since this is exactly what also happens with POST requests with the logistics application I mentioned earlier.
This to me, would hint that maybe something in the response of the HTTP POST makes Power Query automatically launch an another query? I'm really out of ideas what more to check. I feel HTTP POST through Power Query is too powerful of a tool not to function correctly.
let
Source = Json.Document(Web.Contents(https://api.messente.com/v1/omnimessage, [Headers=[#"Content-Type"="application/json", #"Accept"="application/json", #"Authorization"="Basic my_auth_string"],
Content=Json.FromValue(
[to = "+3721234567",
messages = {[channel = "sms", text = "test123"]}
])]))
in
Source
