Paginate Rest API via Offset and Limit method

Anonymous

Not applicable

6 years ago

It seems like you are copying the sample query formula and try to use it with your scenario, right?
I think you need to do some modification on internal functions with their URL query string and returned properties. (your API does not contain 'top, skip, count' parameters)

Please take a look at below formula if it meets to your requirement:

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

Notice: I'm not so sure what type of offset parameter your API used, please choose one of two optional offset steps based on your scenario.

Regards,

Xiaoxin Sheng

Anonymous

Not applicable

6 years ago

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

bwhdegroot

Frequent Visitor

6 years ago

This worked like a charm, many thanks Anonymous !

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"

rafaellopesro

New Member

4 years ago

Senhores, boa noite!

Gostaria de ajuda para fazer este get no power bi funcionar... Abaixo é o retorno da paginação da API.

Gostaria de trazer todos os dados desta tabela da API...

--Total é a quantidade total de registros.

--Offset é o inicio da minha busca

--limit é o maximo que a api retorna de uma vez,...

Ja tentei inumeras formas mas ja estou sem conseguir pensar em uma solução,...

Lmbrando que minha API usar um cabecalho Headers.

let 
    linhasPorPaginas = 250,
    Token = [Headers = [#"access-token"="xxxxxxx", #"secret-access-token"="yyyyyyy", #"content-type"="application/json", #"cache-control"="no-cache"]],
    Limit  = "&limit="   & Text.From(linhasPorPaginas),

    Url = "https://api.vhsys.com/v2/produtos?" & Limit,
    GetJson = (Url) =>
        let 	
           RawData = Web.Contents(Url,   Token),		
            Json    = Json.Document(RawData)
        in  Json,

        Url2 = Url & "&offset=0,",
 
    GetEntityCount = () =>
        let Url   = (Url2  &  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 * linhasPorPaginas)&",", 
			//(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 / linhasPorPaginas),
    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

Forum Discussion

Paginate Rest API via Offset and Limit method

Related content

Dynamic Date Offset Navigator

shortcuts - Refresh Method

Cells Offset

Copy Data Pagination - Offset in Request Body

Paginated Report