Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
SebbyP333
Helper I
Helper I

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

1 ACCEPTED SOLUTION

@SebbyP333  find attached

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

15 REPLIES 15
SoundCloud12
New Member

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
Helper III
Helper III

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
Super User
Super User

@SebbyP333 solution attached. 

 

parry2k_0-1641339465625.png

 

Follow us on LinkedIn and YouTube.gif 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!



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?

parry2k
Super User
Super User

@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 YouTube.gif 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!



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

parry2k
Super User
Super User

@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 YouTube.gif 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.

 



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.

smpa01
Super User
Super User

@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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

@SebbyP333  find attached

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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?

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.