Resolving 500 Internal Server Error on API

[amreet11]

Hi,

I am trying to extract data from a service used by my company via an API. The company in question is providing limited support.

I am calling their API within Power Query to extract daily sales data. However, my company does not hold the historical data, it is all held by the service provider. 

I can use the below code to successfully extract sales data from the API. However, if I attempt to download data for any more than a week or so, I receive a 500 Internal Server Error.

I need to download around 4 years worth of data. Is there a way for me to edit the below code to get around the download size restrictions on their server? I would rather avoid having to download the data a couple of days at a time and pasting it into an excel.

The API docs are available here: https://hapi.tissl.com/#tag/Get-Sales-Data/operation/GetStandardData

Any help here would be much appreciated.

Thanks

let

api_url = "https://hapi.tissl.com/interfaces/generic/sales/XXXXXXXXXXXXXXXXXXXX",

api_key = "XXXXXXXXXXXXX",

body = "{

""site"": {

""id"": ""XXXXXXXXXXXXXXXXXXXX""},

""fromDate"": ""2021-11-19"",

""toDate"": ""2021-11-20"",

""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"

[amreet11]

Thanks for the response. 

Is there any value to including some kind of delay (along these lines: https://medium.com/@AndreAlessi/building-delays-into-power-bi-api-queries-function-invokeafter-and-g...) to minimse the chances of a server timeout?

[ams1 · Accepted solution]

Hi @amreet11 

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.