Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amreet11
Frequent Visitor

Resolving 500 Internal Server Error on API

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_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"
1 ACCEPTED SOLUTION
ams1
Super User
Super User

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.

 

View solution in original post

2 REPLIES 2
amreet11
Frequent Visitor

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
Super User
Super User

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors