Forum Discussion
API call limit - introduce delay
Hi,
I'm using power bi desktop to connect to web services using the from web query. Everything works well before i try to merge queries. I web service I connect to only allows 1 call per 2 second so I think i hit that limitation. Is there a way to introduce a delay to get around this?
Ludvig
Thanks, downloaded Fiddler and it looks like power bi makes a new http call for more or less every operation. Have only taken a couple of programming classes 10 years ago but that would not even been aloud in 101 classes!
I have tried Table.Buffer but it didn't help much in my more complex queries. I guess I will try to keep the queries to raw data and do all calucaltions etc using the modelling functionality and see if that helpes.
Thanks again Chris!
10 Replies
lsuneson No expert in API calls but this might help you - also has a reference to an old similar post
https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/
thanks konstantinos. I have actually seen that post but I couldn't figure out how to use Function.InvokeAfter().
Below is simple exampel of my query, if anyone can give some advice, it woudl be very appriciated.
let
Source = Json.Document(Web.Contents("https://api.skulabs.com/purchase_order/get?limit=100&skip=&sort=")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "distributor_id"}, {"Column1.number", "Column1.distributor_id"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Column1",{"Column1.distributor_id"},MapTable, {"Column1._id"},"NewColumn",JoinKind.LeftOuter)
in
#"Merged Queries"Try something like this:
let Source = ()=> Json.Document(Web.Contents("https://api.skulabs.com/purchase_order/get?limit=100&skip=&sort=")), #"Converted to Table" = Table.FromList(Function.InvokeAfter(Source, #duration(0,0,0,2)), Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "distributor_id"}, {"Column1.number", "Column1.distributor_id"}), #"Merged Queries" = Table.NestedJoin(#"Expanded Column1",{"Column1.distributor_id"},MapTable, {"Column1._id"},"NewColumn",JoinKind.LeftOuter) in #"Merged Queries"This adds a two second delay before calling the web service; the length of the delay is determined by #duration(0,0,0,2) in the "Converted to Table" step.
HTH,
Chris
i have this function:
(Query as text, ResultType as text) => if ResultType = "record" then
let
source = OData.Feed("https://api.security.microsoft.com/api/" & Query, null, [Implementation="2.0", MoreColumns=true]),
#"toList" = {source},
#"toRecord" = Record.FromList(#"toList", {"Record"})
in
#"toRecord"
else
let
source = OData.Feed("https://api.security.microsoft.com/api/" & Query, null, [Implementation="2.0", MoreColumns=true]),
#"toTable" = #table(1, {{source}})
in
#"toTable"
i call this function :let
ODataSource = customODataQuery("Machines","table"),
Column1 = ODataSource{0}[Column1],
#"Added Custom" = Table.AddColumn(Column1, "OnboardedDate", each [firstSeen]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"OnboardedDate", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"GetMissingKbs", "LatestMachineActions", "AvailableMachineActions"}),
#"Expanded vulnerabilities" = Table.ExpandTableColumn(#"Removed Columns", "vulnerabilities", {"id", "name", "description", "severity", "cvssV3", "exposedMachines", "publishedOn", "updatedOn", "publicExploit", "exploitVerified", "exploitInKit", "exploitTypes", "exploitUris", "More Columns"}, {"id.1", "name", "description", "severity", "cvssV3", "exposedMachines", "publishedOn", "updatedOn", "publicExploit", "exploitVerified", "exploitInKit", "exploitTypes", "exploitUris", "More Columns"})in
#"Expanded vulnerabilities"Problem
when i apply to save the cahanges, i run into API call limit. Can someone help please?Estou enfrentando o mesmo problema quando preciso ter uma delay na horas de fazer uma requisacao de minha api
let
Fonte = Json.Document(Web.Contents("https://api.4yousee.com.br/v1/playlists/45", [Headers=[#"Secret-Token"="#(tab)xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
items = Fonte[items],
#"Convertido para Tabela" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 Expandido" = Table.ExpandRecordColumn(#"Convertido para Tabela", "Column1", {"type", "id", "name", "items", "sequence"}, {"Column1.type", "Column1.id", "Column1.name", "Column1.items", "Column1.sequence"}),
#"Column1.items Expandido" = Table.ExpandListColumn(#"Column1 Expandido", "Column1.items"),
#"Column1.items Expandido1" = Table.ExpandRecordColumn(#"Column1.items Expandido", "Column1.items", {"type", "id", "name", "file", "durationInSeconds", "categories", "contentSchedule"}, {"Column1.items.type", "Column1.items.id", "Column1.items.name", "Column1.items.file", "Column1.items.durationInSeconds", "Column1.items.categories", "Column1.items.contentSchedule"}),
#"Column1.items.contentSchedule Expandido" = Table.ExpandRecordColumn(#"Column1.items Expandido1", "Column1.items.contentSchedule", {"endDate"}, {"Column1.items.contentSchedule.endDate"}),
#"Colunas Removidas" = Table.RemoveColumns(#"Column1.items.contentSchedule Expandido",{"Column1.type", "Column1.id", "Column1.items.type", "Column1.items.id", "Column1.items.file", "Column1.items.durationInSeconds", "Column1.items.categories", "Column1.sequence"}),
#"Valor Substituído" = Table.ReplaceValue(#"Colunas Removidas",null,"11/05/2045",Replacer.ReplaceValue,{"Column1.items.contentSchedule.endDate"}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Valor Substituído",{{"Column1.items.contentSchedule.endDate", type date}}),
#"Linhas Agrupadas" = Table.Group(#"Tipo Alterado", {"Column1.name"}, {{"Contagem", each List.Max([Column1.items.contentSchedule.endDate]), type nullable date}}),
#"Colunas Renomeadas" = Table.RenameColumns(#"Linhas Agrupadas",{{"Column1.name", "IANDE"}, {"Contagem", "DATA"}})
in
#"Colunas Renomeadas"
