Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smpa01
Super User
Super User

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 MessageError 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 !!!

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
rbrundritt
Employee
Employee

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.

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.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

It worked. Many thanks !!!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@Xi thank you very much for looking into this. I am testing it out with gateway now. Will give you the feedback soon
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.