I am pulling in the positions of a Portfolio into Power BI and have batch the asset IDs into groups of 50. I've then use the following query to build a custom payload to an API to pull in analytics on the asset IDs. I'm running into an error when I attempt to call the analytics API for the payload.
Query:
let
apiUrl = "https://"& clientEnv & ".blackrock.com/api/analytics/data/climate/v1/data:retrieve",
headers = generateHeaders,
securities = #"Positions - Securities",
Custom1 = Table.AddColumn(securities, "Custom", each "{ #(cr)#(lf)""asOfDate"": """& #"asOfDate" &""", #(cr)#(lf) ""climateRiskRequests"": [#(cr)#(lf) {#(cr)#(lf) ""climateScenarios"": [#(cr)#(lf) {#(cr)#(lf) ""name"": ""RCP 8.5"",#(cr)#(lf) ""percentile"": ""mean"",#(cr)#(lf) ""timeframe"": """&Timeframe&"""#(cr)#(lf) }#(cr)#(lf) ],#(cr)#(lf) ""metricCodes"": [""PR_PRICE_DELTA""],#(cr)#(lf) ""metricType"": ""METRIC_TYPE_PHYSICAL"" #(cr)#(lf) }#(cr)#(lf) ],#(cr)#(lf) ""idType"": """& idType &""",#(cr)#(lf) ""ids"": [#(cr)#(lf) "),
Custom3 = Table.AddColumn(#"Custom1", "Custom.1", each " #(cr)#(lf) ],#(cr)#(lf) ""pageSize"": 0,#(cr)#(lf) ""pageToken"": ""string""#(cr)#(lf)}"),
Custom4 = Table.AddColumn(#"Custom3", "payload", each [Custom] & [Text Before Delimiter] & [Custom.1]),
payload = Table.RemoveColumns(Custom4,{"Text Before Delimiter", "Custom", "Custom.1"}),
results = Table.AddColumn(payload,"Results",each Json.Document(Web.Contents(apiUrl,[Headers=headers, Content=Text.ToBinary([payload])])))
in
results
Error:
Formula.Firewall: Query 'Query1' (step 'results') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Can someone please help me diagnose why I'm getting this issue? I've run this exact query in other environments (see variable client_env) and had no problems.
