Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
I've run this exact query in other environments (see variable client_env) and had no problems.
In how many other environments ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.