cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

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

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]))

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

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

3.3 Try out the Python or R script.

 

Best Regards,

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

View solution in original post

2 REPLIES 2
Microsoft v-jiascu-msft
Microsoft

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

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]))

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

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

3.3 Try out the Python or R script.

 

Best Regards,

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

View solution in original post

Anonymous
Not applicable

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

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors