cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User I
Super User I

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.

 

Capture.PNGError 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

swq.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Web.Contents Mapbox reverse geocoding error in service

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"

 

7.png

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Web.Contents Mapbox reverse geocoding error in service

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"

 

7.png

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Super User I
Super User I

Re: Web.Contents Mapbox reverse geocoding error in service

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

Re: Web.Contents Mapbox reverse geocoding error in service

It worked. Many thanks !!!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors