In my last article, I showed you how to connect the quote endpoint from the Yahoo Finance API to Power BI. By leveraging the documentation and using copy/paste, we successfully connected to the API without a single line of code. In our review of the outcome we noted that our response only brought back data for a single stock.

While helpful, we know you probably want to refresh data for more than one stock. In fact, I happen to have a list of stocks that I am currently watching:

A list of stocks and when I wish I had bought them back in the day.

My next challenge is to take my original query and make it sustainable for however many stocks I have on my watch list.

Options are Plentiful

As with every Microsoft product, I have several different ways that I could manage this challenge. All have their benefits and drawbacks. Let’s look at them each and see which one would work best for my scenario.

Option 1 – Append Individual Queries

The first solution you might have considered is building a separate connection for each of the individual stocks we have in our list. Each query would be individually created and then appended into a master list so that way it stays organized. This solution would work and be effective, but there are drawbacks.

First of all, the biggest draw back is that I have to manually create this query each and every time I want to add a new stock. This does not take a ton of time, but it would require a few minutes of effort each and every time. Not a big deal if you only need to add one or two stocks to the model but it would add up with 20-30 stocks.

The other drawback is that you will have to continue managing the data model. If the list of stocks is dynamic, that means you either need to create a single model with every single stock in it or continue to manage it over time. This is not really sustainable as new stocks enter the market on a regular basis and requires manual maintenance.

Option 2 – Go Back to the Documentation

Sometimes you have some options when it comes to your endpoints for expanding your query. If we go back to look at our documentation for the quote endpoint, you will see that we can specify up to 10 stock symbols in our query.

Note that our API documentation says you can select up to 10 stock symbols all separated with a comma.

Perfect! So instead of having to write out 25 separate queries, I can condense it all into three. That is much more manageable and efficient! Well – almost more efficient…

Remember what I stated above – because we are still specifying the stock symbols for each query we still have to manually intervene every time we make a change to our stock list. This could get difficult especially as we go to maintain our list over time as we might need to remove stocks later. As a result, we will spend time searching for which query to resolve.

Option 3 – Scale Out our Original Query as a Function

The last option we have is to make our current query scalable. How do we make a query scalable? By converting it into a function.

Functions allow us to reuse that query over and over again to simplify our refresh. Acting similar to our first option, we can apply a function to a list of stock symbols and quickly bring back the quote information. Now instead of managing the queries, we are just managing a list of stock symbols. As symbols are added and removed, our data model adjusts accordingly. The result allows us to automate refreshes without having to adjust the data model in the future.

Converting a Query to a Function

Remember how I said you might need some minimal coding? That time has come. We will need to do something you may have never done before – open the advanced editor window in Power Query.

Before we do that, I do want to rename the current query to “Get-StockInfo” so it is a little easier to read. Just update the name on the query settings bar on the right. I like using the word get in front to help quickly identify it as a function versus a query.

Once you have done that, click on Advanced Editor in the home ribbon and strap in for the ride!

Step 1 – Convert the Query to a Function

Our goal is to make this function flexible for any stock symbol. That means we need to require a stock symbol to be called every time the function is leveraged.

When you open the code editor, the first line has the single word “let”. We are going to insert a row above that and put a pair of parentheses followed by an equals sign and greater than sign. This effectively makes the conversion from a query to a function.

()=>
let
    Source = Json.Document(Web.Contents("https://yfapi.net/v6/finance/quote?region=US&lang=en&symbols=MSFT", [Headers=[Accept="application/json", #"X-API-KEY"="bHjSKbiRtr4cOzmOopdrI5TUKX5kMKXU6weUteeL"]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded quoteResponse" = Table.ExpandRecordColumn(#"Converted to Table", "quoteResponse", {"result", "error"}, {"quoteResponse.result", "quoteResponse.error"}),
    #"Expanded quoteResponse.result" = Table.ExpandListColumn(#"Expanded quoteResponse", "quoteResponse.result"),
    #"Expanded quoteResponse.result1" = Table.ExpandRecordColumn(#"Expanded quoteResponse.result", "quoteResponse.result", {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"}, {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"})
in
    #"Expanded quoteResponse.result1"

However, we have not done anything to handle the requirement to pass a stock symbol when the function is called. To require a stock symbol be called, we will simply add a variable called StockSymbol and specify that it be passed in a text format. This will require a stock symbol be passed any time you leverage this function.

(StockSymbol as text)=>
let
    Source = Json.Document(Web.Contents("https://yfapi.net/v6/finance/quote?region=US&lang=en&symbols=MSFT", [Headers=[Accept="application/json", #"X-API-KEY"="bHjSKbiRtr4cOzmOopdrI5TUKX5kMKXU6weUteeL"]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded quoteResponse" = Table.ExpandRecordColumn(#"Converted to Table", "quoteResponse", {"result", "error"}, {"quoteResponse.result", "quoteResponse.error"}),
    #"Expanded quoteResponse.result" = Table.ExpandListColumn(#"Expanded quoteResponse", "quoteResponse.result"),
    #"Expanded quoteResponse.result1" = Table.ExpandRecordColumn(#"Expanded quoteResponse.result", "quoteResponse.result", {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"}, {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"})
in
    #"Expanded quoteResponse.result1"

Step 2 – Make the Connection String Dynamic

Now that we converted the query to a function and required a stock symbol be passed, it is time for us to use it dynamically with the connection string.

On line 3, you see our endpoint url and parameters that specify MSFT as our designated stock. We need to replace MSFT with our new variable. We will simply delete the current symbol, add an ampersand (&) outside of the quotes, and specify StockSymbol to concatenate the values together.

(StockSymbol as text)=>
let
    Source = Json.Document(Web.Contents("https://yfapi.net/v6/finance/quote?region=US&lang=en&symbols=" & StockSymbol, [Headers=[Accept="application/json", #"X-API-KEY"="bHjSKbiRtr4cOzmOopdrI5TUKX5kMKXU6weUteeL"]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded quoteResponse" = Table.ExpandRecordColumn(#"Converted to Table", "quoteResponse", {"result", "error"}, {"quoteResponse.result", "quoteResponse.error"}),
    #"Expanded quoteResponse.result" = Table.ExpandListColumn(#"Expanded quoteResponse", "quoteResponse.result"),
    #"Expanded quoteResponse.result1" = Table.ExpandRecordColumn(#"Expanded quoteResponse.result", "quoteResponse.result", {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"}, {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"})
in
    #"Expanded quoteResponse.result1"

With the dynamic variable placed in the connection string, the function will now pull stock quote information based upon the symbols we have in our list.

Step 3 – Resolve Connection String Errors

If you clicked done and used this function in your report, everything would be fine inside of Power BI Desktop. However, you are going to run into an issue when you publish to the Power BI Service. Because we are using a dynamic parameter, the cloud service is not sure how to handle authentication. The result is an error in the Power BI Service and a report that will not refresh.’

To resolve this issue, we want to split our connection string to resolve this issue. We will essentially split it into two different sections. The first section will serve as the connection string and help with our authentication. The second half will be treated as a dynamic portion of the query and has no bearing on the authentication process.

In short, all we are going to do is move a portion of that connection string to prevent any errors from happening.

(StockSymbol as text)=>
let
    Source = Json.Document(Web.Contents("https://yfapi.net/v6/finance/", [RelativePath="quote?region=US&lang=en&symbols=" & StockSymbol, Headers=[Accept="application/json", #"X-API-KEY"="bHjSKbiRtr4cOzmOopdrI5TUKX5kMKXU6weUteeL"]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded quoteResponse" = Table.ExpandRecordColumn(#"Converted to Table", "quoteResponse", {"result", "error"}, {"quoteResponse.result", "quoteResponse.error"}),
    #"Expanded quoteResponse.result" = Table.ExpandListColumn(#"Expanded quoteResponse", "quoteResponse.result"),
    #"Expanded quoteResponse.result1" = Table.ExpandRecordColumn(#"Expanded quoteResponse.result", "quoteResponse.result", {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"}, {"language", "region", "quoteType", "quoteSourceName", "triggerable", "currency", "firstTradeDateMilliseconds", "postMarketChangePercent", "postMarketTime", "postMarketPrice", "postMarketChange", "regularMarketChange", "regularMarketChangePercent", "regularMarketTime", "regularMarketPrice", "regularMarketDayHigh", "regularMarketDayRange", "regularMarketDayLow", "regularMarketVolume", "regularMarketPreviousClose", "bid", "ask", "bidSize", "askSize", "fullExchangeName", "financialCurrency", "regularMarketOpen", "averageDailyVolume3Month", "averageDailyVolume10Day", "fiftyTwoWeekLowChange", "fiftyTwoWeekLowChangePercent", "fiftyTwoWeekRange", "fiftyTwoWeekHighChange", "fiftyTwoWeekHighChangePercent", "fiftyTwoWeekLow", "fiftyTwoWeekHigh", "dividendDate", "marketState", "earningsTimestamp", "earningsTimestampStart", "earningsTimestampEnd", "trailingAnnualDividendRate", "trailingPE", "trailingAnnualDividendYield", "epsTrailingTwelveMonths", "epsForward", "epsCurrentYear", "priceEpsCurrentYear", "sharesOutstanding", "bookValue", "fiftyDayAverage", "fiftyDayAverageChange", "fiftyDayAverageChangePercent", "twoHundredDayAverage", "twoHundredDayAverageChange", "twoHundredDayAverageChangePercent", "marketCap", "forwardPE", "priceToBook", "sourceInterval", "exchangeDataDelayedBy", "pageViewGrowthWeekly", "averageAnalystRating", "tradeable", "priceHint", "exchange", "shortName", "longName", "messageBoardId", "exchangeTimezoneName", "exchangeTimezoneShortName", "gmtOffSetMilliseconds", "market", "esgPopulated", "displayName", "symbol"})
in
    #"Expanded quoteResponse.result1"

This is a subtle but important move. By placing the dynamic content inside the relative path, we have eliminated confusion during the authentication process. Power BI will authenticate on the connection string in place and then concatenate the relative path to complete the request. You only need to do this when your queries are dynamic, but it is an important step in the process!

Step 4 – Test the Function

After clicking done, your view in Power Query will have changed. Note that the query list now shows an “fx” symbol for function and you no longer have a tabular list of data. In fact, you have a place to enter a stock symbol. This allows us to quickly test to see if our function is working. Go ahead and enter MSFT and click invoke to see it work.

The outcome from converting a query to a function.

After you invoke the function, you will be taken to a new query called “Invoked Function” and can see the stock data for Microsoft. That was just to test and see if it works. We can go ahead and delete that query because will not need it again.

Applying the Function

With a working function in hand, it is time to apply it to our data model. I already have a list of stocks in Excel, so I will import that list to Power BI. If your stock list is somewhere else, that is perfectly fine. Just get that list of symbols inside of Power BI.

Once imported, go to your “Add Column” tab and select “Invoke Custom Function”. Select your function from the list. You will notice that the StockSymbol variable will appear. We need to specify which column of values will be passed to that variable. Select the symbol column and click OK.

Setting up the custom function to use the stock symbols from the spreadsheet.

At this point, you may be prompted about data privacy. If you do, just select ignore privacy levels for now as this is a demonstration.

You should have a new column with the word table highlighted inside of it. We can go ahead and expand those values and confirm the current stock quote info is available for each different symbol.

And just like that you created your own function and applied it in Power Query!

Next Steps

With a working function, what is left to discuss? As I stated at the beginning of this series – each API is like a snowflake. They all are unique and have their own challenges. This is a single example of how to connect a REST API to Power BI. There are challenges and other considerations you must balance when creating your reports. In my next post, I will dive into challenges you will see when leveraging other APIs inside of Power BI.

Until then, was this your first time creating a function? Did you find it challenging? Do you have other applications for a function inside of Power BI? Tell me in the comments below!