Forum Discussion
How to reference a table column within an API url
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?
8 Replies
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?
So in the desktop service i should enter the code for the online service becuase thats where it will end up?
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.
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
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
