Using a list of ID´s as a changing part of URL in Query for Rest Api Get-method

[Anonymous]

Let´s imagine that I am attempting to create a dynamic report for my organization that would get data from Finlands national open Rest Api resource for service data ,https://api.palvelutietovaranto.suomi.fi/swagger/ui/index.html , connecting to it´s Get-methods by URL in Power Query. 

What I am trying to do is to have the URL´s ID ending change according to a separate list of ID´s in Power Query.  

For example we have: /api/v10/ServiceChannel/{id} 

And I have tried with: /api/v10/ServiceChannel/?filter=id eq 'id_list'" , (id_list being the name of my list of ID codes)

Though what I have tried doesn´t work, I just get a general list of all the service channels without any reference to the list of ID´s. Also I have tried creating a parameter from the id-list but I get the same result.

So I want the id_list to roll nicely in the url end and work dynamicly so that it can be easily updated if the list changes. I have  watched some videos and read forum post regarding this kind of thing and similar cases (string query, parameters etc.), but somehow I just don´t seem to get it. Is it so that I would need some M-language function? If so, how should it be? 

(Is this even possible?)

Also that example API restricts the number of items in a list to 100, I would want to have much more ID´s in the list, will that be a problem? How could I get over that in the most efficient way? Or is cutting the list of ID´s into separate lists the only option? (And would that be reasonable...)

Thank you for help!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

1. Are there any other APIs that can fetch all data at one call? It would be a performance bottleneck to call the API hundreds of times. 

2. The limit 100 is from the definition of the API. I'm afraid we can't overwrite it.

3. There could be several ways you can try.

3.1 List all the IDs and add a new column like below.

Json.Document(Web.Contents("https://api.palvelutietovaranto.suomi.fi/api/v10/Service/" & [Column1.id]))

3.2 Hide these steps in the code, only output the final result.

3.3 Try out the Python or R script.

Best Regards,

[Anonymous]

Thank you a lot! This was useful! Got it working with creating a new column according to the id-list. (3.1)