cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SebbyP333
Helper I
Helper I

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

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.

 

 

 

3 REPLIES 3
ibarrau
Super User
Super User

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,


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

Happy to help!

LaDataWeb Blog

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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors