Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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"
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:
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!
Solved! Go to Solution.
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.
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.
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
13 |