cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielntamasi
Frequent Visitor

Change parameter back and forth between 2-3 values for a sequence of query requests in Power Query

Hi,

 

I'm running a couple of API requests that pulls all the stock information from a list for hundreds of stocks. Unfortunately, I'm running into a couple of issues:

  • Because my the API provider only allows 5 calls per minute, I need to use the InvokeAfter feature to delay my call.
  • However due to the number of calls I'm making, my query load time exceeds the 120 minutes on the Power BI service.

I'm trying to cut down on the time by using multiple API keys to pull the data faster. Unfortunately, I don't know how to parameterize the pull of API keys dynamically in a Power Query script to switch between 2-3 different keys after each call.

 

 

Meaning:

  1. First stock is pulled with APIkey_1
  2. Second stock is pulled with APIkey_2
  3. Third stock is pulled with APIkey_3
  4. Forth stock is pulled with APIkey_1 (as i reuse just 2-3 keys without exceeding the allotted 5 calls per API key per minute).
  5. Fitfh stock is pulled with APIkey_2 (as i reuse just 2-3 keys without exceeding the allotted 5 calls per API key per minute).
  6. Sixth stock is pulled with APIkey_3 (as i reuse just 2-3 keys without exceeding the allotted 5 calls per API key per minute).
  7. ... and so on.

FYI, here is my query below, relevant pieces are highlighted with red and blue. Also, my query runs fine other than the issue mentioned above.

 

Thank you for your answer in advance!

 

Best,

DT

 

let
    Source = Json.Document(Web.Contents("https://data.opendatasoft.com/api/records/1.0/search/?dataset=cusip-isin-sp-500-nasdaq-100%40public-us&rows=600")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"datasetid", "recordid", "fields", "record_timestamp"}, {"datasetid", "recordid", "fields", "record_timestamp"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"cusip_number", "ticker", "company_name", "isin_code"}, {"cusip_number", "ticker", "company_name", "isin_code"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fields",{"datasetid", "recordid", "cusip_number", "isin_code", "record_timestamp"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Function.InvokeAfter(()=>Json.Document(Web.Contents("https://www.alphavantage.co/",
    [RelativePath="query?function=TIME_SERIES_DAILY_ADJUSTED&outputsize=full&apikey=" & APIkey_1 & "&symbol=" & [ticker]])), #duration(0,0,0,6))),
    //I would like to dynamically change the "APIkey_1" above to "APIkey_2", then "APIkey_3", then back to "APIkey_1", then "APIkey_2", then "APIkey_3", and so on.
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Time Series (Daily)"}, {"TimeSeries"}),
    #"Expanded TimeSeries" = Table.ExpandRecordColumn(#"Expanded Custom", "TimeSeries", Table.ColumnNames(Table.FromRecords(#"Expanded Custom"[TimeSeries]))),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded TimeSeries", {"ticker", "company_name"}, "Attribute", "Value"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"5. adjusted close", "7. dividend amount"}, {"Value.5. adjusted close", "Value.7. dividend amount"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Attribute", "Date"}, {"Value.5. adjusted close", "Adjusted close"}, {"Value.7. dividend amount", "Dividend"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Adjusted close", type number}, {"Dividend", type number}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Changed Type1", {{"Date", each Text.End(_, 10), type text}}),
    #"Parsed Date" = Table.TransformColumns(#"Extracted Last Characters",{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Parsed Date", each true),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"company_name", "Company name"}, {"ticker", "Ticker"}})
in
    #"Renamed Columns1"
3 REPLIES 3
Community Support
Community Support

Re: Change parameter back and forth between 2-3 values for a sequence of query requests in Power Que

Hi danielntamasi,

I saw you seems use parameter in your query  apikey=" & APIkey_1 & "&symbol=" & [ticker]. You could try to use query like below

let dynamic1= (api) =>
let
    Source = Json.Document(Web.Contents("https://data.opendatasoft.com/api/records/1.0/search/?dataset=cusip-isin-sp-500-nasdaq-100%40public-us&rows=600")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"datasetid", "recordid", "fields", "record_timestamp"}, {"datasetid", "recordid", "fields", "record_timestamp"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"cusip_number", "ticker", "company_name", "isin_code"}, {"cusip_number", "ticker", "company_name", "isin_code"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fields",{"datasetid", "recordid", "cusip_number", "isin_code", "record_timestamp"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Function.InvokeAfter(()=>Json.Document(Web.Contents("https://www.alphavantage.co/",
    [RelativePath="query?function=TIME_SERIES_DAILY_ADJUSTED&outputsize=full&apikey=api&symbol=" & [ticker]])), #duration(0,0,0,6))),
    //I would like to dynamically change the "APIkey_1" above to "APIkey_2", then "APIkey_3", then back to "APIkey_1", then "APIkey_2", then "APIkey_3", and so on.
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Time Series (Daily)"}, {"TimeSeries"}),
    #"Expanded TimeSeries" = Table.ExpandRecordColumn(#"Expanded Custom", "TimeSeries", Table.ColumnNames(Table.FromRecords(#"Expanded Custom"[TimeSeries]))),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded TimeSeries", {"ticker", "company_name"}, "Attribute", "Value"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"5. adjusted close", "7. dividend amount"}, {"Value.5. adjusted close", "Value.7. dividend amount"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Attribute", "Date"}, {"Value.5. adjusted close", "Adjusted close"}, {"Value.7. dividend amount", "Dividend"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Adjusted close", type number}, {"Dividend", type number}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Changed Type1", {{"Date", each Text.End(_, 10), type text}}),
    #"Parsed Date" = Table.TransformColumns(#"Extracted Last Characters",{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Parsed Date", each true),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"company_name", "Company name"}, {"ticker", "Ticker"}})
in
    #"Renamed Columns1"
in
    dynamic1

Then you could manage parameter api to use different api key.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

danielntamasi
Frequent Visitor

Re: Change parameter back and forth between 2-3 values for a sequence of query requests in Power Que

Zoe,

Thank you for your answer. Unfortunately, I need to change the between the API keys, whilst still running the same query. In other words: I run the query for ~600 stocks. I need the API keys to change between 2-3 values in a loop while going through the ~600 stock.

Something like this:
1st stock: 1st key
2nd stock: 2nd key
3rd stock: 3rd key
4th stock: 1st key
5th stock: 2nd key
6th stock: 3rd key...
...
598th stock: 1st key
599th stock: 2nd key
600th stock: 3rd key
danielntamasi
Frequent Visitor

Re: Change parameter back and forth between 2-3 values for a sequence of query requests in Power Que

@dax, any suggestions for this?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors