Forum Discussion

bwhdegroot's avatar
bwhdegroot
Frequent Visitor
6 years ago

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...
Need Help
bwhdegroot's avatar
bwhdegroot
Frequent Visitor
6 years ago

This worked like a charm, many thanks Anonymous !

rafaellopesro's avatar
rafaellopesro
New Member
4 years ago

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"