Paginate Rest API via Offset and Limit method

[bwhdegroot]

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.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=XXX

This is our CRM system. As there is a limit of 500, I want to paginate using the offset/limit method. I tried several examples but they all seam to fail with me.

= List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=MYKEY")) else Json.Document(Web.Contents("https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=MYKEY&offset="&Last_Key)), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1)

Can anyone tell me what I am doing wrong here? I get a list of 50 records as per the standard limit on the API. The second record shows an error that the operator cannot be applied to text and number.

Many thanks!

[amitchandak]

@bwhdegroot , refer if these can help

http://sqlcodespace.blogspot.com/2017/09/power-bipower-query-api-response.html
https://datachant.com/2016/06/27/cursor-based-pagination-power-query/
https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

[bwhdegroot]

@amitchandak Unfortunately I have been going through all these links already, but no success to far.

Also tried this example, adjusted it a bit but also seems not to work properly.

The API documentation shows the following specs of the API:

Param Type Default Required

include	string		optional
offset	int	0	optional
limit	int	50	optional
access_token	string		required

limit = max 500

https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=XXX

Response

Field Type Description

status	string	ok or error
total_count	int	Total result count
jobs	array	Array of jobs

let 
   Url         = "https://api.searchsoftware.nl/v2/jobs?include=categories|contacts&access_token=MYKEY",
    EntitiesPerPage = 50,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = Url & "$count=true&$top=0",
            Json  = GetJson(Url),
    Count = Json[#"total_count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = Url & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, 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

[v-shex-msft]

HI @bwhdegroot,

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

[bwhdegroot]

Hi @v-shex-msft 

Thank you! This is almost what I need and I see it retrieving data. THe only observation is that based on my 836 records and limit per page of 50, I see 17 pages. Nevertheless, these 17 pages all have the same data in there. How can de code be adapted so it loops the offset to show different pages?

Many thanks!

[v-shex-msft]

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

[rafaellopesro]

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

[bwhdegroot]

This worked like a charm, many thanks @v-shex-msft !

[rafaellopesro]

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"