Web.Contents Mapbox reverse geocoding error in service

[smpa01]

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/

https://blog.crossjoin.co.uk/2018/11/06/the-credentialquery-option-for-web-contents-in-power-bi-cust...

My current privacy setting on the service is following

Can someone please help. Any help is greatly appreciated !!!

[rbrundritt]

You might want to remove your API key from these posts or cycle your API with Mapbox so as to disable it, otherwise you risk someone copying it from here and generating usage on your account.

[smpa01]

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.

[Anonymous · Accepted solution]

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
    GetCountry

Use:

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

[smpa01]

It worked. Many thanks !!!

[smpa01]

@Xi thank you very much for looking into this. I am testing it out with gateway now. Will give you the feedback soon