POST-request creates DUPLICATE records on first run

[crossover]

Hi, with some kind help from this board, I was able to put together a functional POST-request over PowerQuery. I want to create records to REST-based API over PowerQuery. So, the focus is on input, not receiving data back, even though the query does return JSON-data with the newly created record. Query response is only necessary for "no-error" verification.

The problem I face is that every time I change data and launch the query for the first time, I get 2 duplicate records posted to API. When refreshing the query again, it works normally and generates only a single new record. The only scenario when it seemingly works as expected is if I go to PQ Advanced Editor, adjust data and press "Done". Query is refreshed and I get single record at API. However if I even edit hardcoded query contents and press "Save + Load", I will get data posted in API twice. Again, any further refresh generates a single record.

Obviously the contents of the query shouldn't be hardcoded, but rather I need to use data linked to Excel range (such as "API_address" below). Unfortunately same applies here - if I change data in linked Excel range and refresh query, I get duplicate results, however refreshing the query again (w/o changing data), result is 1 extra record (just stating thefact, though it's functionally useless).

This is very confusing to me. I tend to think it's not really API-side problem since I have never seen this before (like testing this over Postman). What could be causing this and is there a way to avoid it?

let
    Source = Json.Document(Web.Contents("https://myapi.com/tasks/", [Headers=[Authorization="mytoken",
	#"Content-Type"="application/json"],
									Content=Json.FromValue(
									[account = "https://myapi.com/tasks/account/",
                                    //address = [raw_address = API_address]
                                    address = [raw_address = "myStreet 10"]
                                    ])])),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

[Smauro]

Hi @crossover 

If this issue exists with the query you posted, and you're not going to change anything in this query, then the issue is probably on the evaluation step of the query -- where Power BI tries to see if everything is alright before actually running it.

You could try this and see if duplicates are not added:

let

    Source = Json.Document(Web.Contents("https://myapi.com/tasks/",

        [

            Headers = [

                Authorization="mytoken",

                #"Content-Type" = "application/json" ],

            Content = Json.FromValue( [

                account = "https://myapi.com/tasks/account/",

                //address = [ raw_address = API_address ]

                address = [ raw_address = "myStreet 10" ] ])

        ])),

    #"Converted to Table" = Record.ToTable(Source),

    tableType = type table [

                    #"Name" = Any.Type,

                    #"Value" = Any.Type

                ],

    #"Table View" = Table.View (

        null,

        [

            GetType = () => tableType,

            GetRows = () => #"Converted to Table",

            OnTake = (n as number) =>

                if n = 0 then #table ( tableType, {} )

                else Table.FirstN ( #"Converted to Table", n )

        ]

    ) 

in

    #"Table View"

Or, since you actually don't want to POST anything while editing the query, use this OnTake:

OnTake = (n as number) =>  #table ( tableType, {} )

Beware that if you do this then you won't see any results in the query editor.

[crossover]

@Smauro thanks for that. Your suggestion on the evaluation step being the culprit looks sensible. Just a note - I'm not launching the query from Power BI, but from Excel. However as they both use PowerQuery, I assume it doesn't matter here. I tried adjusting the M-code according to your pointers and while the query still runs and data is posted to API, same problem still prevails, when input data is changed (in Excel cell), first query always posts duplicate records to API.

While I'm still intrigued on how to overcome this from technical standpoint, I managed to find a workaround. The API does accept "external ID" to be sent in POST request contents and I have added it. Data is most likely still forwarded twice, however given the same ID is provided in contents, second concurrent POST only overwrites the first record and doesn't generate duplicate records anymore. As long as ID-s are always unique, I shouldn't have problems in unwanted overwriting of data and including timestamp in the ID should take care of that.

[Icey]

Hi @crossover ,

---

The problem I face is that every time I change data and launch the query for the first time, I get 2 duplicate records posted to API. When refreshing the query again, it works normally and generates only a single new record. 

...

Obviously the contents of the query shouldn't be hardcoded, but rather I need to use data linked to Excel range (such as "API_address" below). Unfortunately same applies here - if I change data in linked Excel range and refresh query, I get duplicate results, however refreshing the query again (w/o changing data), result is 1 extra record (just stating thefact, though it's functionally useless).

---

I know little about the internal operation principle in your scenario. But I have an idea: is this issue caused by the cache, something like we open the .xlsx file?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[crossover]

Hmm... interesting. But not sure how to follow up on this. While it's true, query IS run from open Excel workbook, how would that affect the query in a way I described? Any suggestions on how to test this?

Just to clarify - the operation itself does not open or operate other workbooks. The user launches the query from a single (opened) Excel workbook towards a web API.