Forum Discussion

CarlosWagner's avatar
CarlosWagner
Frequent Visitor
5 years ago
Solved

API Json REST - com tabela de registros infinitos

Estou com um problema ao requisitar um endereço URL E Estou seguindos os passos: 1º Criei uma tabela com os endereços;         startIndex = 1 & count = 100 startIndex = 101 & ...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    5 years ago

    The documentation seems ok, but as you have noticed it only returns 500  (well, 460) rows.  Can you talk to someone at that company to ask them for an exampe of how they use the API?  I think there are other parameters, or some other part of the process that we are missing.

     

    EDIT:  Try this version, this seems to work :

     

     

    let
    Source = List.Generate(()=>1, each _ < 5000, each _+500),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each Json.Document(Web.Contents("http://novaindy105186.protheus.cloudtotvs.com.br:4040/rest/RTPWBI04?startindex=" & Text.From([Column1]) & "&count=" & Text.From([Column1]+499)))),
    #"Expanded Data" = Table.ExpandRecordColumn(#"Added Custom", "Data", {"data"}, {"Data.data"}),
    #"Expanded Data.data" = Table.ExpandListColumn(#"Expanded Data", "Data.data"),
    #"Expanded Data.data1" = Table.ExpandRecordColumn(#"Expanded Data.data", "Data.data", {"id", "Filial", "Item", "Produto", "DescProduto", "TipoProduto", "UM", "Quantidade", "VlrPrecoVenda", "VlrUnitario", "VlrDesconto", "VlrTotal", "Custo", "TipoSaida", "NPedido", "Cliente", "Loja", "Nome", "Doc", "Serie", "TipoDoc", "DtEmissao", "CodGrupo", "DescGrupo", "CodTabPreco", "Vendedor"}, {"id", "Filial", "Item", "Produto", "DescProduto", "TipoProduto", "UM", "Quantidade", "VlrPrecoVenda", "VlrUnitario", "VlrDesconto", "VlrTotal", "Custo", "TipoSaida", "NPedido", "Cliente", "Loja", "Nome", "Doc", "Serie", "TipoDoc", "DtEmissao", "CodGrupo", "DescGrupo", "CodTabPreco", "Vendedor"})
in
    #"Expanded Data.data1"

     

     

    In my opinion the "count"  parameter is wrongly named in their API. It should be called "endIndex".

lbendlin's avatar
lbendlin
Super User
5 years ago

Here is the pedestrian way (without recursion).  Your data is rather large so the recursion would hit memory issues soon. Does the API have a call to find out the maximum ID? Do you have a link to the API documentation? I don't think "startIndex"  is a valid field.

 

 

 

let
    Source = List.Generate(()=>1, each _ < 5000, each _+500),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each Json.Document(Web.Contents("http://novaindy105186.protheus.cloudtotvs.com.br:4040/rest/RTPWBI04?startIndex=" & [Column1] & "&count=500"))),
    #"Expanded Data" = Table.ExpandRecordColumn(#"Added Custom", "Data", {"data"}, {"Data.data"}),
    #"Expanded Data.data" = Table.ExpandListColumn(#"Expanded Data", "Data.data"),
    #"Expanded Data.data1" = Table.ExpandRecordColumn(#"Expanded Data.data", "Data.data", {"id", "Filial", "Item", "Produto", "DescProduto", "TipoProduto", "UM", "Quantidade", "VlrPrecoVenda", "VlrUnitario", "VlrDesconto", "VlrTotal", "Custo", "TipoSaida", "NPedido", "Cliente", "Loja", "Nome", "Doc", "Serie", "TipoDoc", "DtEmissao", "CodGrupo", "DescGrupo", "CodTabPreco", "Vendedor"}, {"id", "Filial", "Item", "Produto", "DescProduto", "TipoProduto", "UM", "Quantidade", "VlrPrecoVenda", "VlrUnitario", "VlrDesconto", "VlrTotal", "Custo", "TipoSaida", "NPedido", "Cliente", "Loja", "Nome", "Doc", "Serie", "TipoDoc", "DtEmissao", "CodGrupo", "DescGrupo", "CodTabPreco", "Vendedor"})
in
    #"Expanded Data.data1"