Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bwhdegroot
Frequent Visitor

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.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!

8 REPLIES 8

@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

includestring optional
offsetint0optional
limitint50optional
access_tokenstring required

limit = max 500

 

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

Response

Field Type Description

statusstringok or error
total_countintTotal result count
jobsarrayArray 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

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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. 

rafaellopesro_2-1645326652871.png

 

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

 

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

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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.