Hello Folks!
I'm using Power Query to retrieve specific financial data (bills that are not paid "situation=3") from my ERP's API.
The API data Query is "normal", like http://erpsite.com/api/financial.data.php?token=1234&format=json&situation=3&page=1
My problem is that their API retrieve only the first 100 records, so if I have 102 records, for example, I need to query [...]&page=2 to get the table with 2 records. I have never reached so far 100 records, but that has just happened this month.
Here is the basic code, where I get data from Excel (token changes every week, that's why I have a Dynamic "URL")
let
apiAddress = Excel.CurrentWorkbook(){[Name="URL"]}[Content]{0}[Column1],
Source = Json.Document(Web.Contents(apiAddress), 65001),
#"Converted to Table" = Record.ToTable(Source),
#"Value Expandido" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"status_processamento", "status", "pagina", "numero_paginas", "notas_servico"}, {"Value.status_processamento", "Value.status", "Value.pagina", "Value.numero_paginas", "Value.notas_servico"}),
in
#"Value Expandido"
This code gives me a table like this, with a List of service_notes that I expand to get the 100 records filtered with "situation=3":
| name | status_processamento | status | id | page | number_pages | service_notes |
| retorno | 3 | OK | 345787 | 1 | 2 | List |
Since I'm from Brasil, in the code "pagina" means "page" and "numero_paginas" means "number_pages".
As you can see, the table gives me the page number that I used in the URL [...]&page=1 (actually, if I put no page number, it is the same as page=1) and the total number of pages, that has only been 1 so far.
I need a solution to dynamically loop and give me a single table with all the records, "fixing" this 100 records limitation.
So what I think should be the steps:
- Inicial Query without page parameter or with page=1 and get the number_pages value.
- Loop number_pages times changing dynamically the URL with page=[actual value]
- Combine each query in a single table and gives me a
I know now my URL parameter includes &page= parameter, but of course I should remove that and dynamically set it.
How can I perform something like that?
Thanks for your attention!
