Iterate and Join Tables Over Dynamic API Pages With Power Query

[Syndicate_Admin]

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!

[Syndicate_Admin]

Hi @glauberludwig ,

Please see if this document can help:

Defining and using parameters in API page - Dynamics 365 Business Central Forum Community Forum

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Yes, that way also could help, but I was able to perform my solution with this video.

https://www.youtube.com/watch?v=05yhwnuCjRw

This was exacly what I needed: get the page number and them perform dynamic queries.

Thanks!