Scheduled refresh for Dynamic data sources - JSON with API token

[IvanS]

Hi guys,

as absolute beginner in customized Power Query commands, I would need your help. I am trying to workaround the issue with schedule refresh for dynamic data sources using the Web.Contents and RelativePath for my JSON API requests. However I am stuggling to have it in place.

Below is the business cases:

- The web URL consists of the same information except the page (which needs to increment by 1 up to approx. 1000).

https://companyname.fakturownia.pl/invoices.json?api_token=XXXXXXXXXXXXXXXX/companyname &page=1

This is frustrating as the load takes ages (with invoking the function) - ideally I would like to get all data at once. Would there by any simplification which would load data in a more efficient way?

Anyway, what I am looking for is to get the data from all pages automatically via schedule refresh on Power BI service.

https://companyname.fakturownia.pl/invoices.json?api_token=XXXXXXXXXXXXXXXX/companyname &page=1

https://companyname.fakturownia.pl/invoices.json?api_token=XXXXXXXXXXXXXXXX/companyname &page=2

https://companyname.fakturownia.pl/invoices.json?api_token=XXXXXXXXXXXXXXXX/companyname &page=3

.................................

https://companyname.fakturownia.pl/invoices.json?api_token=XXXXXXXXXXXXXXXX/companyname &page=1000

Currently I am using the solution by MyOnlineTrainingHub (https://www.youtube.com/watch?v=VkDpwSGWPmk&lc=UgwDfQp6xLf3WgtC_-d4AaABAg) however I would need to push this to PBI service with auto refresh.

Any solution is much appreciated!

[lbendlin]

Please show a sanitized version of your Power Query code.

[IvanS]

Hi @lbendlin ,

as mentioned, I used the code from Scrape Data from Multiple Web Pages with Power Query - YouTube - the only issue is, that I need to refresh the data manually via Desktop. Any attempt to set up schedule refresh will lead to failure.

The current code is as per template below:

The desired solution is to have a dynamic link (where the sequence of the pages in 1,2,3 ...).

I found two articles to this topic that are using Web.Content and RelativePath however I am an newbie to the custom Power Query commands.

Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI Chris Webb'...

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

[lbendlin]

Study these documents and apply the methodology they propose. Then report back.

It is very difficult / close to impossible to help with API connection issues without having access to that web service.