Forum Discussion

crossover's avatar
crossover
Advocate I
5 years ago

POST-request creates DUPLICATE records on first run

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, n...
Need Help
Show and Tell
Smauro's avatar
Smauro
Solution Sage
5 years ago

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's avatar
    crossover
    Advocate I
    5 years ago

    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.