Dynamically changing url to get the data based on relevant parameter

[asifrasool]

Hi Everyone, I have a data source system from where I have to fetch the data into powerbi. I have to connect via 'Web' connector in powerbi to make 2 calls to get the data. In otherwords its a rest api call.

1) First call will fetch me the data as shown in the SS below. This call is working fine. The following numbers are called PZN.

2) The second call will bring me the data related to this number, the general url will be like 

I added another parameter and made this url like this, (second parameter is essential for this api call to run)

here I want powerbi to read the pzn number from 1 api call (show in number 1) and pick the first number and put it in front of pzn=XXXXXXXXXXX (e.g. drug?catalogShort=MMI&pzn=14295542) I want to do it like this for all of the PZN returned in first call.

Is there any possibility I can do this work in PowerBI? If yes then any leads would be highly appreciated.

Thanks and Kind Regards,

Asif

[johncolley]

Hi @asifrasool ,

You can create a new column using Concatenate to create a url string. I.e

URL = Concatenate("e.g. drug?catalogShort=MMI&pzn=",'PZN Column')

This will generate a url for each PZN number in your table.

If this answer solves your problem please mark it as a solution!

[asifrasool]

@johncolley Thank you so much for the response,
Actually the thing is that I have to make rest api calls, with first call I'll get the list of PZN numbers. Then I have to make api calls related to each PZN number. like this,

first call for 

drug?catalogShort=MMI&pzn=1

second call for

drug?catalogShort=MMI&pzn=2

third call for

drug?catalogShort=MMI&pzn=3

and so on untill last PZN.

[johncolley]

Hi @asifrasool , Once you get your list of numbers you may be able to parameterise it for the rest of the calls, more detail here: https://www.phdata.io/blog/how-to-parameterize-data-sources-power-bi/ 

There also may be some useful information in these threads:

https://community.powerbi.com/t5/Desktop/RestApi-with-Session-Authentication/td-p/624578

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i... 

My knowledge in this area is fairly limited so hopefully these help!