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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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