Forum Discussion
Solved
How to use an API string with reference to a dynamic table column?
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 a...
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"
Where do I reference the table column with the list of stock codes within your query?
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?
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?