Hi team,
I am connecting 2 queries: #1st return latitude/longitude using https://api.ipgeolocation.io/ipgeo API, #2nd using geo-location return from 1st to get weather data from https://api.open-meteo.com/v1/forecast where lat/lnt are both parameters.
I was able to make this work by tweaking privacy levels options, but it only worked in PBI Desktop. But it gives an error as soon as I publish to MyWorkplace. I am not admin of BI portal, so I don't see much options to tweak privacy online. Any thoughts on how to make this work? Greatly appreicated
Formula.Firewall: Query 'Query1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Here are my 2 queries (a bit long):
#1st
let
Source = Json.Document(Web.Contents("https://api.ipgeolocation.io/ipgeo",
[ Headers = [ #"Accept-Encoding" = "gzip" ],
Query = [ apiKey = "xxxxxx" ]
]
)),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ip", type text}, {"continent_code", type text}, {"continent_name", type text}, {"country_code2", type text}, {"country_code3", type text}, {"country_name", type text}, {"country_capital", type text}, {"state_prov", type text}, {"district", type text}, {"city", type text}, {"zipcode", Int64.Type}, {"latitude", type number}, {"longitude", type number}, {"is_eu", type logical}, {"calling_code", Int64.Type}, {"country_tld", type text}, {"languages", type text}, {"country_flag", type text}, {"geoname_id", Int64.Type}, {"isp", type text}, {"connection_type", type text}, {"organization", type text}, {"currency", type any}, {"time_zone", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"continent_code", "country_code2", "country_code3", "district", "is_eu", "calling_code", "country_tld", "languages", "isp", "connection_type", "organization", "currency", "time_zone"})
in
#"Removed Columns"
#2nd
let
Source = Json.Document( Web.Contents( "https://api.open-meteo.com/",
[ RelativePath = "v1/forecast",
Headers = [ #"Accept-Encoding" = "gzip" ],
Query = [
latitude = "-10.00",
longitude = "-50.00",
current_weather = "true",
temperature_unit = "fahrenheit",
daily = {"temperature_2m_min", "temperature_2m_max"},
past_days = "0",
forecast_days = "7",
start_date = "2023-04-10",
end_date = "2023-04-12",
timeformat = "iso8601",
timezone = "GMT"
]
]
)
),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"latitude", type number}, {"longitude", Int64.Type}, {"generationtime_ms", type number}, {"utc_offset_seconds", Int64.Type}, {"timezone", type text}, {"timezone_abbreviation", type text}, {"elevation", Int64.Type}, {"current_weather", type any}, {"daily_units", type any}, {"daily", type any}}),
#"Expanded current_weather" = Table.ExpandRecordColumn(#"Changed Type", "current_weather", {"temperature", "windspeed", "winddirection", "weathercode", "is_day", "time"}, {"current_weather.temperature", "current_weather.windspeed", "current_weather.winddirection", "current_weather.weathercode", "current_weather.is_day", "current_weather.time"})
in
#"Expanded current_weather"
