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
emerson89
Helper I
Helper I

Paginate API - FOUR to Next Offset Item

Hi,
I created this api paginated below. It works correctly, but in the OFFSET property, I need to stipulate instead of the number of the next sequence number of the record, for example, for the second page, the number 251, the next record of the second page, and so on.

  • My record limit per page is 250

  • The field totalItems returned me the total of records, for example: 4500

  • I divide the total number of records by the total number of records per page, to get to know how many pages my api has: pageRange = {0..Number.RoundUp(totalItems / 250)}

  • When going to the second page, what happens in the API below, is that the records of the second page are coming repeated, because I should instead use the number 1 (referring to the second page), pass the number 251, and then, when doing the loop again, pass the number 501, until finishing the whole sequence (this parameter in the api is: offset=.

My code:

 

let
    ufnCallAPI = (offSet) =>
        let
            query = Web.Contents("https://api.vhsys.com/v2/pedidos?offset=" & Number.ToText(offSet)  &  "&limit=250", 
            [Headers=[#"access-token"="OCKNYbAMaDgLBZBSQPCOGPWOXGSbdO", #"secret-access-token"="XXXXXXXXXXX"]]),
            result = Json.Document(query)
        in
            result,

        tmpResult = ufnCallAPI(1),

        auxTotal1 = Record.ToTable(tmpResult),
        Value = auxTotal1{2}[Value],
        auxTotal2 = Value[total],

        totalItems = auxTotal2 - 1,
        pageRange = {0..Number.RoundUp(totalItems / 250)},
        pages = List.Transform(pageRange, each ufnCallAPI(_)),
        pages2 = Table.FromList(pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        pages3 = Table.ExpandRecordColumn(pages2, "Column1", {"code", "status", "paging", "data"}, {"Column1.code", "Column1.status", "Column1.paging", "Column1.data"}),
        pages4 = Table.ExpandListColumn(pages3, "Column1.data"),
        pages5 = Table.RemoveColumns(pages4,{"Column1.code", "Column1.status", "Column1.paging"}),
        data = Table.ExpandRecordColumn(pages5, "Column1.data", {"id_ped", "id_pedido", "id_cliente", "nome_cliente", "id_local_retirada", "id_local_cobranca", "vendedor_pedido", "vendedor_pedido_id", "listapreco_produtos", "valor_total_produtos", "desconto_pedido", "desconto_pedido_porc", "peso_total_nota", "peso_total_nota_liq", "frete_pedido", "valor_total_nota", "valor_baseICMS", "valor_ICMS", "valor_baseST", "valor_ST", "valor_IPI", "condicao_pagamento_id", "condicao_pagamento", "frete_por_pedido", "transportadora_pedido", "id_transportadora", "data_pedido", "prazo_entrega", "referencia_pedido", "obs_pedido", "obs_interno_pedido", "status_pedido", "contas_pedido", "comissao_pedido", "estoque_pedido", "ordemc_emitido", "data_cad_pedido", "data_mod_pedido", "id_aplicativo", "id_pedido_aplicativo", "lixeira"}, {"id_ped", "id_pedido", "id_cliente", "nome_cliente", "id_local_retirada", "id_local_cobranca", "vendedor_pedido", "vendedor_pedido_id", "listapreco_produtos", "valor_total_produtos", "desconto_pedido", "desconto_pedido_porc", "peso_total_nota", "peso_total_nota_liq", "frete_pedido", "valor_total_nota", "valor_baseICMS", "valor_ICMS", "valor_baseST", "valor_ST", "valor_IPI", "condicao_pagamento_id", "condicao_pagamento", "frete_por_pedido", "transportadora_pedido", "id_transportadora", "data_pedido", "prazo_entrega", "referencia_pedido", "obs_pedido", "obs_interno_pedido", "status_pedido", "contas_pedido", "comissao_pedido", "estoque_pedido", "ordemc_emitido", "data_cad_pedido", "data_mod_pedido", "id_aplicativo", "id_pedido_aplicativo", "lixeira"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(data,{{"id_ped", type text}, {"id_pedido", Int64.Type}, {"nome_cliente", type text}, {"valor_total_produtos", type text}}),
    #"Valor Substituído" = Table.ReplaceValue(#"Tipo Alterado",".",",",Replacer.ReplaceText,{"valor_total_produtos"}),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Valor Substituído",{{"valor_total_produtos", Currency.Type}}),
    #"Valor Substituído1" = Table.ReplaceValue(#"Tipo Alterado1",".",",",Replacer.ReplaceValue,{"desconto_pedido", "desconto_pedido_porc", "peso_total_nota", "peso_total_nota_liq", "frete_pedido", "valor_total_nota", "valor_baseICMS", "valor_ICMS", "valor_baseST", "valor_ST", "valor_IPI"}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Valor Substituído1",{{"desconto_pedido", Currency.Type}, {"desconto_pedido_porc", Currency.Type}, {"peso_total_nota", Currency.Type}, {"peso_total_nota_liq", Currency.Type}, {"frete_pedido", Currency.Type}, {"valor_total_nota", type text}, {"valor_baseICMS", Currency.Type}, {"valor_ICMS", Currency.Type}, {"valor_baseST", Currency.Type}, {"valor_ST", Currency.Type}, {"valor_IPI", Currency.Type}, {"prazo_entrega", type text}, {"data_pedido", type date}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Tipo Alterado2",{"id_aplicativo", "id_pedido_aplicativo", "lixeira"}),
    #"Tipo Alterado3" = Table.TransformColumnTypes(#"Colunas Removidas",{{"valor_total_nota", type text}}),
    #"Valor Substituído2" = Table.ReplaceValue(#"Tipo Alterado3",".",",",Replacer.ReplaceText,{"valor_total_nota"}),
    #"Tipo Alterado4" = Table.TransformColumnTypes(#"Valor Substituído2",{{"valor_total_nota", Currency.Type}})
   
in
#"Tipo Alterado4"

 

 

I need alter this line to include de FOUR/WHILE for the item "ufnCallAPI(_)", :

pages = List.Transform(pageRange, each ufnCallAPI(_)),

for example, of item above:

 

List.Transform(pageRange, each ufnCallAPI(250)),
List.Transform(pageRange, each ufnCallAPI(500)),
List.Transform(pageRange, each ufnCallAPI(750)),
up to the total number = totalItems...

 

 

and inclued a FOR/WHILE to modified my API to not pass the number of the next page, but the number of the beginning of the list of the next item start (offset).

Thanks very much!

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @emerson89 

How about tring this method:

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

 

Best Regards

Maggie

Hey @v-juanli-msft ,

 

Thank you for your help. I looked at the post that indicated me, and I really believe that it would also solve my problem. However, I used the following solution:

https://community.powerbi.com/t5/Desktop/Power-Query-M-function-in-Paginate-API/m-p/1091882


Thanks.

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.

Top Solution Authors
Top Kudoed Authors