Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 !!!
Solved! Go to 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
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |