How to use an API string with reference to a dynamic table column?

[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 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

What is the best way to do this please?

[SoundCloud12]

To use an API string with a dynamic table column, first, get the data from the dynamic column using its variable or identifier. Then, build the API string by adding the dynamic column's value to the right API endpoint or parameters. Lastly, send the API request with the dynamic data to get or update the necessary information.

[lkshck]

Hey,

I want to do nearly the same but with Invoice data. So I got the API which is .../billing/invoices which delivers a Json Object with all past Invoices with limited amount of information. Import information which I receive via this call is the Invoice IDs which I need to use to query it again like .../billing/invoices/<id> to get details of the invoice which I need. Now the question would be if I have the table with all Invoice IDs available, how should the next table and Query in PowerBI look like to query the API for the details for every invoice and putting it into one table.

[parry2k]

@SebbyP333 solution attached. 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[Wolly]

I'm trying to incorporate this solution as well for a basket of stocks.  I keep getting the error "Expression.Error: The import Changed Type matches no exports. Did you miss a module reference?" when i put my own stock tickers in in place of the McDonalds etc.  What could i be missing?  Thx for the help!

[SebbyP333]

Thank you but I am trying to reference a column in a seperate table.  Please find attached your file Stock API 

with a table called Ticker Universe.  I want to reference the column 'Ticker Universe', 'EOD Stock Code'.  The reason being is that this is imported from another file which gets maintatined outside of Power BI.

I have uploaded to Power BI online service.  This dynamic design doesn't allow for scheduled refresh.  Is there another solution possible to create the API URL and being able to schedule refresh online?

[parry2k]

@SebbyP333 The solution I had posted previously, was built based on any number of stock codes, using a table of stock codes and passing to a function. FYI

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[SebbyP333]

thank you is it possible to provide me with a power bi file with the instructions you have provided.  It doesn't seem to be working on my end

[parry2k]

@SebbyP333 create a list of stock codes as you mentioned, and then create a function and pass stock code as the parameter, and in the function use function parameter to create the API string, let's call it GetSourceCodeData

(stockcode) =>
let
  Source = "https://eodhistoricaldata.com/api/eod/" & stockcode & "?from=2006-01-05&period=d&fmt=json&api_token=OeAFFmMliFG5orCUuwAKQ8l4WWFQ67YX"
in
  Source

in the source code table add a new column:

GetSourceCodeData([SourceCodeColumn])

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smpa01]

@SebbyP333  you can dynamically pass on the string to the query

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"

[SebbyP333]

Where do I reference the table column with the list of stock codes within your query?

[smpa01 · Accepted solution]

@SebbyP333  find attached

[SebbyP333]

Hey, I am sorry to bother you again but I am trying to replace your code with the actual table and column in my power bi file.  I thought i could work it out but i am struggling.  The table is called 'Ticker Universe' and the column i need to reference is 'EOD Stock Code'.  Where do i replace your code with the actual table and column i have?

[smpa01]

@SebbyP333  find attached

[SebbyP333]

The file you sent over seems to have just changed the name of the table which was produced as a result of the function.  I want to reference a table and column with my list of stock codes which is already in Power BI Query.  

I believe your solution means i have to create a list in a source table within Power BI?

[SebbyP333]

Thats really really pretty cool! Thank you for your help.  If i upload this to the online service will this still work fine with scheduled updates or do you think there could be an issue?