Forum Discussion
Solved
Web.Contents Mapbox reverse geocoding error in service
Hello,
My sample data is following which is a concatenation of Longitude and Latitude. It is a table extracted from SQL server.
| LongLatText |
| -72.982050,42.539070 |
| -122.954850,50.128120 |
I am trying to do a reverse geocoding to see on which country that long lat falls by using a mapbox geocoding API on this documentation - https://docs.mapbox.com/api/search/#reverse-geocoding
# Mapbox documenation on reverse geocoding
# A basic reverse geocoding request
# Retrieve places near a specific location
$ curl "https://api.mapbox.com/geocoding/v5/mapbox.places/-73.989,40.733.json?access_token=pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA"
Following this, I did a custom function as following
GetCountry = let
Source = (address as text)=>let
Source = Json.Document(Web.Contents("https://api.mapbox.com/geocoding/v5/mapbox.places?access_token=pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA",[RelativePath ="/"&address&".json", Query=[access_token="pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA"]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{2}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "type", "place_type", "relevance", "properties", "text", "place_name", "bbox", "center", "geometry", "context"}, {"Column1.id", "Column1.type", "Column1.place_type", "Column1.relevance", "Column1.properties", "Column1.text", "Column1.place_name", "Column1.bbox", "Column1.center", "Column1.geometry", "Column1.context"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each Text.Contains([Column1.id], "country")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1.place_name"})
in
#"Removed Other Columns"
in
Source
When I invoke this on my source data in power bi desktop, it works out perfectly.
However, when I publish the data source in the service, it generates an error.
Error Message
I studied couple of blog posts to fix this
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
My current privacy setting on the service is following
Can someone please help. Any help is greatly appreciated !!!
Hi smpa01 ,
It seems like a common authorization issue(power bi service does not allow you setting anonymous authorization mode on detail API URL), please change your query formula to apply anonymous on 'root path'.
Modified custom function:
let GetCountry = (address as text,apikey as text)=> let rootpath="https://api.mapbox.com", //apikey="pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA", relativePath="/geocoding/v5/mapbox.places/"&address&".json", Source = Json.Document(Web.Contents(rootpath,[RelativePath =relativePath,Query=[access_token=apikey]])), #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{2}[Value], #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "type", "place_type", "relevance", "properties", "text", "place_name", "bbox", "center", "geometry", "context"}, {"Column1.id", "Column1.type", "Column1.place_type", "Column1.relevance", "Column1.properties", "Column1.text", "Column1.place_name", "Column1.bbox", "Column1.center", "Column1.geometry", "Column1.context"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each Text.Contains([Column1.id], "country")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1.place_name"}) in #"Removed Other Columns" in GetCountryUse:
let apikey="pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA", address="-72.982050,42.539070"&".json", Source = Web.Contents("https://api.mapbox.com", [RelativePath ="/geocoding/v5/mapbox.places/"&address,Query=[access_token=apikey]]), #"Converted to Table" = Record.ToTable(Json.Document(Source)), Value = #"Converted to Table"{2}[Value], #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "type", "place_type", "relevance", "properties", "text", "place_name", "bbox", "center", "geometry", "context"}, {"Column1.id", "Column1.type", "Column1.place_type", "Column1.relevance", "Column1.properties", "Column1.text", "Column1.place_name", "Column1.bbox", "Column1.center", "Column1.geometry", "Column1.context"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each Text.Contains([Column1.id], "country")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1.place_name"}) in #"Removed Other Columns"Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code
Regards,
Xiaoxin Sheng
5 Replies
Hi smpa01 ,
It seems like a common authorization issue(power bi service does not allow you setting anonymous authorization mode on detail API URL), please change your query formula to apply anonymous on 'root path'.
Modified custom function:
let GetCountry = (address as text,apikey as text)=> let rootpath="https://api.mapbox.com", //apikey="pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA", relativePath="/geocoding/v5/mapbox.places/"&address&".json", Source = Json.Document(Web.Contents(rootpath,[RelativePath =relativePath,Query=[access_token=apikey]])), #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{2}[Value], #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "type", "place_type", "relevance", "properties", "text", "place_name", "bbox", "center", "geometry", "context"}, {"Column1.id", "Column1.type", "Column1.place_type", "Column1.relevance", "Column1.properties", "Column1.text", "Column1.place_name", "Column1.bbox", "Column1.center", "Column1.geometry", "Column1.context"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each Text.Contains([Column1.id], "country")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1.place_name"}) in #"Removed Other Columns" in GetCountryUse:
let apikey="pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA", address="-72.982050,42.539070"&".json", Source = Web.Contents("https://api.mapbox.com", [RelativePath ="/geocoding/v5/mapbox.places/"&address,Query=[access_token=apikey]]), #"Converted to Table" = Record.ToTable(Json.Document(Source)), Value = #"Converted to Table"{2}[Value], #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "type", "place_type", "relevance", "properties", "text", "place_name", "bbox", "center", "geometry", "context"}, {"Column1.id", "Column1.type", "Column1.place_type", "Column1.relevance", "Column1.properties", "Column1.text", "Column1.place_name", "Column1.bbox", "Column1.center", "Column1.geometry", "Column1.context"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each Text.Contains([Column1.id], "country")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1.place_name"}) in #"Removed Other Columns"Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code
Regards,
Xiaoxin Sheng
- Thanks for the advice. The API key O shared here is not my personal API key. This is the example API key mapbox publicly shared on the reverse geocoding example on their site. But thanks for watching out.
