Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to Solution.
@SebbyP333 find attached
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.
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.
@SebbyP333 solution attached.
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
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?
@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
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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])
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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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?
@SebbyP333 find attached
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?
@SebbyP333 find attached
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?
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?
User | Count |
---|---|
120 | |
95 | |
88 | |
73 | |
66 |
User | Count |
---|---|
138 | |
112 | |
110 | |
98 | |
94 |