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
Anonymous's avatar
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

rafaellopesro's avatar
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