How to reference a table column within an API url

[SebbyP333]

Hello Power BI Forum,

I have the following API url to pull in stock price data using the web function on Power BI Desktop.  The part of the API key in red, bold and underlined is the stock code and you can only have one stock code called at a time.  I would like to have a seperate table with a complete list of my stock codes and then automatically replace the stock code in the API key to the list i have created.

https://eodhistoricaldata.com/api/eod/MCD.US?from=2006-01-05&period=d&fmt=json&api_token=OeAFFmMliFG5orCUuwAKQ8l4WWFQ67YX

The list of stock codes would be like following

Stock Code                        Stock Name

MCD.US                             McDonalds Corp

AAPL.US                            Apple Corp

MSFT.US                            Microsoft Corp

What is the best way to do this, if it is possible to do?

[lbendlin]

power bi service refresh dynamic data sources at DuckDuckGo

[SebbyP333]

It concerns me now that whatever i have done in desktop service is going to work in the online service.  I would not guess that they would function differently.  Ultimately, I would like to build a desktop solution and upload to the app environment so i can use all the other fucntionality that comes with the dashboards and alerts for example.  Do you have any advice on how to reconstruct the API url to reference the stock code.  By the way the api key provided in the original post only works for appl.us or mcd.us stock codes as it is a test url.  I look forward to any further assistance that you can provide

[v-easonf-msft]

Hi, @SebbyP333 

Could you please tell me whether your problem has been solved?

If yes, please accept the helpful answer as solution to close this thread. You could also share your own solution here.

Best Regards,
Community Support Team _ Eason

[SebbyP333]

So in the desktop service i should enter the code for the online service becuase thats where it will end up?    

[lbendlin]

It all depends on your use case. If you eventually plan to run a scheduled refresh of your dataset in the Power BI service then you have to write your Power Query code in the desktop (or in a dataflow) in a way that avoids the formula firewall issues. There are lots of good articles on the interwebs about this topic.

[lbendlin]

This is easy to do on the desktop. You take your list of stock symbols, then add a custom column where you construct the URL for each and call Web.Contents() on that  URL.

In the service however you will hit the formula firewall and will have to rewrite the Web.Contents call to use relativepath instead.

So - where does it need to work? Desktop is sufficient? 

[SebbyP333]

I am building the report in Power BI desktop but once complete i will upload to Power BI service in the App environment.  WIll that cause an issue?

[lbendlin]

Yes, it will. The service won't be able to refresh your dataset unless you make the code changes I mentioned.