Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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"
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.
User | Count |
---|---|
18 | |
11 | |
5 | |
4 | |
3 |