Alternative way to dynamically pass API url to allow online scheduled refresh

[SebbyP333]

Hello Power BI Forum,

I was given a Power BI desktop solution by the community here to allow me to do the following.  The problem is that on the online service schedule refresh is not possible to do.  Is there another way to achieve the desired outcome and allow scheduled refresh?

Import stock price data using the web function on Power BI Desktop.  The part of the API url 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 url.  Then i should get a table for each and every stock price data that is in the list of stock codes.

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 and i would like to reference the stock code column within the API url.

Stock Code                        Stock Name

MCD.US                             McDonalds Corp

AAPL.US                            Apple Corp

MSFT.US                            Microsoft Corp

The solution i was given was:

let
   Stock = "MCD.US",
    Source = Json.Document(Web.Contents("https://eodhistoricaldata.com/api/eod/"&Stock&"?from=2006-01-05&period=d&fmt=json&api_token=OeAFFmMliFG5orCUuwAKQ8l4WWFQ67YX")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "open", "high", "low", "close", "adjusted_close", "volume"}, {"date", "open", "high", "low", "close", "adjusted_close", "volume"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"adjusted_close", type number}, {"volume", Int64.Type}})
in
    #"Changed Type"

However, when i upload to PowerBi Online the scheduled refresh is not possible.  Is there any other way to construct a API url by referencing a column in a table to allow me to automate the individual stock price data and use the refresh functionality.

[ibarrau]

Hi. Dynamic datasets can't always be scheduled. However there is a common approach to use. You need to breakdown the URL to make the a single one for all of the requests with different codes splitting by relative path and query (parameters).

You have a guide on how to break down the url in the web.content function here:
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

I'm not so sure about the way you get the first codes combined with this in order to say "it will work". I would try it because I'm not that sure, but it's possible that would work.

I hope that helps,

[SebbyP333]

Thank you for the advice and article.  I will check it out to see if i can get it to work

[v-janeyg-msft]

Hi, @SebbyP333 

Did you check ibarrau's advice? Did he answer your question? If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Community Support Team _ Janey