cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

READ DATA WITH PAGINATE API JSON

Hi,

I don't have much experience with M language, and I need to read a Paginated API.
The first part I managed to do, getting it to list the data for all the pages, but I need that in the same function all the lines that were pages with this first part of the code are listed.

I can connect to the API and return the data normally, however, limiting to 250 lines and with the page specifies that I send in the call.

 

So, I made the code below to page my API, so I could list all the data.
However, I am not able to make the API return the data line by line, as I don't know how to proceed after this line: pages = List.Transform (pageRange, each ufnCallAPI (_)):

 

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

        tmpResult = ufnCallAPI(1),
        /* totalItems = tmpResult[count], */
        totalItems = 5000,
        pageRange = {1..Number.RoundUp(totalItems/250)},
        pages = List.Transform(pageRange, each ufnCallAPI(_))

in
   pages

 

The API return, testing on Postman, is as follows, for example:

{
    "code": 200,
    "status": "success",
    "paging": {
        "total_count": 3,
        "total": 1026,
        "offset": 0,
        "limit": 3,
        "limit_max": 250
    },
    "data": [
        {
            "id_ped": 5741699,
            "id_pedido": 1,
            "id_cliente": 7943198,
            "nome_cliente": "TONI ",
            "id_local_retirada": 0,
            "id_local_cobranca": 0,
            "vendedor_pedido": "",
            "vendedor_pedido_id": 0,
            "listapreco_produtos": 0,
            "valor_total_produtos": "25.60",
            "desconto_pedido": "0.00",
            "desconto_pedido_porc": "0.00",
            "peso_total_nota": "1250.00",
            "peso_total_nota_liq": "0.000000",
            "frete_pedido": "0.00",
            "valor_total_nota": "25.60",
            "valor_baseICMS": "25.60",
            "valor_ICMS": "3.07",
            "valor_baseST": "0.00",
            "valor_ST": "0.00",
            "valor_IPI": "0.00",
            "condicao_pagamento_id": 0,
            "condicao_pagamento": 1,
            "frete_por_pedido": 9,
            "transportadora_pedido": "",
            "id_transportadora": 0,
            "data_pedido": "2018-12-28",
            "prazo_entrega": "",
            "referencia_pedido": "",
            "obs_pedido": "",
            "obs_interno_pedido": "",
            "status_pedido": "Em Aberto",
            "contas_pedido": 0,
            "comissao_pedido": 0,
            "estoque_pedido": 0,
            "ordemc_emitido": 0,
            "data_cad_pedido": "2018-12-28 09:58:26",
            "data_mod_pedido": "2019-01-03 11:14:46",
            "id_aplicativo": null,
            "id_pedido_aplicativo": null,
            "lixeira": "Sim"
        },
        {
            "id_ped": 5762097,
            "id_pedido": 2,
            "id_cliente": 7967023,
            "nome_cliente": "Teste",
            "id_local_retirada": 0,
            "id_local_cobranca": 0,
            "vendedor_pedido": "",
            "vendedor_pedido_id": 0,
            "listapreco_produtos": 0,
            "valor_total_produtos": "95.70",
            "desconto_pedido": "0.00",
            "desconto_pedido_porc": "0.00",
            "peso_total_nota": "400.00",
            "peso_total_nota_liq": "0.000000",
            "frete_pedido": "0.00",
            "valor_total_nota": "95.70",
            "valor_baseICMS": "95.70",
            "valor_ICMS": "11.48",
            "valor_baseST": "0.00",
            "valor_ST": "0.00",
            "valor_IPI": "0.00",
            "condicao_pagamento_id": 0,
            "condicao_pagamento": 0,
            "frete_por_pedido": 9,
            "transportadora_pedido": "",
            "id_transportadora": 0,
            "data_pedido": "2019-01-03",
            "prazo_entrega": "",
            "referencia_pedido": "",
            "obs_pedido": "",
            "obs_interno_pedido": "",
            "status_pedido": "Em Aberto",
            "contas_pedido": 0,
            "comissao_pedido": 0,
            "estoque_pedido": 0,
            "ordemc_emitido": 0,
            "data_cad_pedido": "2019-01-03 11:24:57",
            "data_mod_pedido": "2019-01-03 12:17:30",
            "id_aplicativo": null,
            "id_pedido_aplicativo": null,
            "lixeira": "Sim"
        },
        {
            "id_ped": 5762446,
            "id_pedido": 3,
            "id_cliente": 7967023,
            "nome_cliente": "Teste",
            "id_local_retirada": 0,
            "id_local_cobranca": 0,
            "vendedor_pedido": "NILSON ",
            "vendedor_pedido_id": 83224,
            "listapreco_produtos": 0,
            "valor_total_produtos": "36.55",
            "desconto_pedido": "0.00",
            "desconto_pedido_porc": "0.00",
            "peso_total_nota": "0.00",
            "peso_total_nota_liq": "0.000000",
            "frete_pedido": "0.00",
            "valor_total_nota": "36.55",
            "valor_baseICMS": "0.00",
            "valor_ICMS": "0.00",
            "valor_baseST": "0.00",
            "valor_ST": "0.00",
            "valor_IPI": "0.00",
            "condicao_pagamento_id": 0,
            "condicao_pagamento": 0,
            "frete_por_pedido": 9,
            "transportadora_pedido": null,
            "id_transportadora": 0,
            "data_pedido": "2019-01-03",
            "prazo_entrega": "5",
            "referencia_pedido": "próximo  as proximidades dos próximos",
            "obs_pedido": "Faturar a partir do dia 10/01",
            "obs_interno_pedido": null,
            "status_pedido": "Em Aberto",
            "contas_pedido": 0,
            "comissao_pedido": 0,
            "estoque_pedido": 0,
            "ordemc_emitido": 0,
            "data_cad_pedido": "2019-01-03 11:50:38",
            "data_mod_pedido": "2019-01-03 12:17:30",
            "id_aplicativo": null,
            "id_pedido_aplicativo": null,
            "lixeira": "Sim"
        }
    ]
}


Can someone help me with the rest of the code please?

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: READ DATA WITH PAGINATE API JSON

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: READ DATA WITH PAGINATE API JSON

Looks like you need to Parse JSON code. @ImkeF @edhans 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: READ DATA WITH PAGINATE API JSON

Hi,

 

I still have this problem, could someone help me please?

Highlighted
Helper I
Helper I

Re: READ DATA WITH PAGINATE API JSON

Hi,

 

It's me again,

I was able to paginate my api, however, it still contains an error, because instead of my API paginating with the page number, it is paged through OFFSET (that is, for every 250 items, I can paginate for the next page). Can someone help me to solve the question below?

 

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 page, 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 = (nPage) =>
        let
            query = Web.Contents("https://api.vhsys.com/v2/pedidos?offset=" & Number.ToText(nPage)  &  "&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 lines:
pageRange = {0..Number.RoundUp(totalItems / 250)},
pages = List.Transform(pageRange, each ufnCallAPI(_)),

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 page (offset)

 

I believe that in this part of the code pageRange = {0..Number.RoundUp (totalItems / 250)}, I need to leave something like:

for (i = 0; i <totalItems, i ++)
{
pageRange = ???
}


Thanks very much!

 

Highlighted
Helper I
Helper I

Re: READ DATA WITH PAGINATE API JSON

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.