Forum Discussion
Paginate Rest API via Offset and Limit method
Hello all, I know this has been covered quite a frequent amount of times, but it seems I cannot get it to work with my api. Currently I am trying to connect to the following API: https://api.sea...
HI bwhdegroot,
After double-check on my code, I think it may be caused by the typo. Your API use '&' character to link query string parameters but I have typing '$' character(at GetPage function) which may not works.
Please try to use the following codes to confirm it they fixed the duplicate page data issue:
let
MYKEY="xxxxxx",//token string
EntitiesPerPage = 500,
Token= "&access_token=" & MYKEY,
Limit="&limit=" & Text.From(EntitiesPerPage),
Url = "https://api.searchsoftware.nl/v2/jobs?include=categories|contacts" & Limit,
GetJson = (Url) =>
let
RawData = Web.Contents(Url & Token),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = Url & "&offset=0" & Token,
Json = GetJson(Url),
Count = Json[#"total_count"]
in
Count,
GetPage = (Index) =>
let
//(option A)offset equal to previous row count
offset = "&offset=" & Text.From(Index * EntitiesPerPage),
//(option B)offset equal to page numer
//offset = "&offset=" & Text.From(Index),
Url = Url & offset & Token,
Json = GetJson(Url),
Value = Json[#"jobs"]
in
Value,
EntityCount = GetEntityCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
Regards,
Xiaoxin Sheng
This worked like a charm, many thanks Anonymous !
Funcinou comigo desta forma
let Url = "https://api.vhsys.com/v2/categorias?lixeira=nao&limit=1", Headers = [#"access-token"="xxxx", #"secret-access-token"="yyyy", #"content-type"="application/json", #"cache-control"="no-cache"], response = Web.Contents(Url, [Headers = Headers]), jsonResponse = Json.Document(response), paging = jsonResponse[paging], total_geral = paging[total], offset = 0, limit=250, #"Pegando Total Maximo" = total_geral, #"Criando Lista" = List.Generate(() => offset, each _ < total_geral, each _ +limit), #"Convertido para Tabela" = Table.FromList(#"Criando Lista", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Colunas Renomeadas" = Table.RenameColumns(#"Convertido para Tabela",{{"Column1", "lista_offset"}}), #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"lista_offset", type text}}), Buscando_Dados = Table.AddColumn(#"Tipo Alterado", "Return", each Json.Document(Web.Contents("https://api.vhsys.com/v2/categorias?order=id_categoria&sort=desc&limit=250&lixeira=nao&offset="&[lista_offset] , [Headers=[ #"access-token"="xxxx", #"secret-access-token"="yyyy", #"content-type"="application/json"]]))), #"Return Expandido" = Table.ExpandRecordColumn(Buscando_Dados, "Return", {"data"}, {"data"}), #"data Expandido" = Table.ExpandListColumn(#"Return Expandido", "data"), #"data Expandido1" = Table.ExpandRecordColumn(#"data Expandido", "data", {"id_categoria", "atalho_categoria", "nome_categoria", "status_categoria", "data_cad_categoria", "data_mod_categoria", "lixeira", "subcategorias"}, {"id_categoria", "atalho_categoria", "nome_categoria", "status_categoria", "data_cad_categoria", "data_mod_categoria", "lixeira", "subcategorias"}), #"Colunas Removidas" = Table.RemoveColumns(#"data Expandido1",{"lista_offset"}), #"Linhas Classificadas" = Table.Sort(#"Colunas Removidas",{{"nome_categoria", Order.Ascending}}) in #"Linhas Classificadas"
