Resolving 500 Internal Server Error on API

Hi Anonymous 

One quick way would be to FIRST convert your query to a function that takes fromDate and toDate as parameters, like this:

let
getPage = (fromDate as text, toDate as text) => let
 
api_url = "https://hapi.tissl.com/interfaces/generic/sales/XXXXXXXXXXXXXXXXXXXX",
api_key = "XXXXXXXXXXXXX",
 
body = "{
""site"": {
""id"": ""XXXXXXXXXXXXXXXXXXXX""},
""fromDate"": """ & fromDate & """,
""toDate"": """ & toDate & """,
""unitcode"": """",
""siteLocationCode"": """",
""tipsAsServiceCharge"": ""false"",
""useSeqNumber"": ""true""
}",
 


data= Json.Document(Web.Contents(api_url,
[
 
 
Headers = [#"ApiKey"=api_key,#"Content-Type"="application/json"], Timeout=#duration(0,5,0,0),
Content = Text.ToBinary(body)]
)
),
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"transactionid", "unitid", "sitelocationcode", "tradingdate", "time", "timefact", "terminalcode", "terminaldesc", "recordactivitycode", "receiptcode", "checkcode", "tablecode", "revenuecentrecode", "revenuecentredesc", "transactiontypecode", "salesitemid", "salesitemPLU", "salesitemGUID", "salesitemdesc", "tendertypecode", "tendertypedesc", "deductioncode", "deductiondesc", "covers", "qty", "currency", "listprice", "tax", "pricepaid", "deduction", "tenderamount", "costpricetheo", "listpriceconv", "taxconv", "pricepaidconv", "deductionconv", "tenderamountconv", "costpricetheoconv", "ordertypedesc", "menuband", "majorgroupdesc", "familygroupdesc", "subgroupdesc", "tabowner", "tabownerdesc", "originaltabowner", "originaltabownerdesc", "oldtablecode", "prevtransactioncode", "authorisedby", "textfield", "guestdesc", "guestcode", "timesenttoprep", "bumptime", "universaltimeslotid", "timeslotdesc", "transactionstartend", "isDeleted", "customfield1", "customfield2", "customfield3", "customfact1", "customfact2", "datefact", "checkID", "customfield4", "customfield5", "customfield6", "customfield7", "customfield8", "customfield9", "customfield10"}, {"transactionid", "unitid", "sitelocationcode", "tradingdate", "time", "timefact", "terminalcode", "terminaldesc", "recordactivitycode", "receiptcode", "checkcode", "tablecode", "revenuecentrecode", "revenuecentredesc", "transactiontypecode", "salesitemid", "salesitemPLU", "salesitemGUID", "salesitemdesc", "tendertypecode", "tendertypedesc", "deductioncode", "deductiondesc", "covers", "qty", "currency", "listprice", "tax", "pricepaid", "deduction", "tenderamount", "costpricetheo", "listpriceconv", "taxconv", "pricepaidconv", "deductionconv", "tenderamountconv", "costpricetheoconv", "ordertypedesc", "menuband", "majorgroupdesc", "familygroupdesc", "subgroupdesc", "tabowner", "tabownerdesc", "originaltabowner", "originaltabownerdesc", "oldtablecode", "prevtransactioncode", "authorisedby", "textfield", "guestdesc", "guestcode", "timesenttoprep", "bumptime", "universaltimeslotid", "timeslotdesc", "transactionstartend", "isDeleted", "customfield1", "customfield2", "customfield3", "customfact1", "customfact2", "datefact", "checkID", "customfield4", "customfield5", "customfield6", "customfield7", "customfield8", "customfield9", "customfield10"})
in
#"Expanded Column1"

in
    getPage

(place above code in a blank query and name the query getPage)

Then, in another query, referenece a table (including hardcoded like below) that will have 1 row for each fromDate/toDate pair and just add a column to invoke that function we created earlier, like this:

let
    Source = Table.FromRecords({
        [fromDate="2021-11-19", toDate="2021-11-20"],
        [fromDate="2021-11-21", toDate="2021-11-22"]
        // and so forth...
    }),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each getPage([fromDate], [toDate]))
in
    #"Added Custom"

The above will run your API request once for each row in the from/to table = MANY requests! = and if everything is OK, you'll have to additionally expand the "Custom" column to MERGE together all records.

Note above I've "simulated" a timespan of one day per request.

If you say 1 week is ok, try with 1 week (less rows = less requests) => for 4 years you'll have like 53 * 4 = 212 rows

Please mark this as ANSWER if it helped.