Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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.
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?
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |