Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I have a list of addresses and some of them cannot plotted on powerbi's map (I assume that my addresses are not correct).
I tried to use Bingmaps Api to add the Lat-Long Columns with a lookup function.
Unfortunately, the function stops when it cannot plot an adress.
I cannot extract a full table with the errors to identify my wrong addresses. It's a nightmare.
Anybody would have a solution to help me please?
Bryan
Solved! Go to Solution.
Hi @Anonymous,
Latest update: I made further changes to the function so that it handles special characters in the address such as & and /
To install my changes please follow these steps:
(1) create a new Query, and rename it to "PointLookupNew": (you also have to insert your API key)
let PointLookUpNew = (address as text) => let key = "<insert your API key here>", address2 = Uri.EscapeDataString(address), Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations?q="& address2 &"&o=xml&key=" &key)), #"Type changed" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}), ResourceSets = #"Type changed"{0}[ResourceSets], ResourceSet = ResourceSets{0}[ResourceSet], #"Type changed1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}), Resources = #"Type changed1"{0}[Resources], Location = Resources{0}[Location], #"Type changed2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}), Point = #"Type changed2"{0}[Point], #"Type changed3" = Table.TransformColumnTypes(Point,{{"Latitude", type text}, {"Longitude", type text}}), exec = try #"Type changed3" in if exec[HasError] then #table( {"Latitude","Longitude"}, {{"error","error"}} ) else exec[Value] in PointLookUpNew
(2) call this new function in the query:
let Source = Excel.Workbook(File.Contents("C:\Adresses.xlsx"), null, true), #"Adresses" = Source{[Item="Adresses",Kind="Sheet"]}[Data], #"Headlines" = Table.PromoteHeaders(#"Adresses_Sheet", [PromoteAllScalars=true]), #"Type changed" = Table.TransformColumnTypes(#"Headlines",{{"ID", Int64.Type},{"Adress", type text}}), #"Function called" = Table.AddColumn(#"Type modifié", "fxPointLookUp", each PointLookUpNew([Adress])), #"fxPointLookUp inserted" = Table.ExpandTableColumn(#"Function called", "fxPointLookUp", {"Latitude", "Longitude"}, {"fxPointLookUp.Latitude", "fxPointLookUp.Longitude"}) in #"fxPointLookUp inserted"
You will find that many of the invalid addresses (that contained special characters) now become valid.
In addition, the query will process all addresses in your list, and display the word "error" when an address truely cannot be geocoded.
Hi @Anonymous, have you tried catching errors with the "try" statement in the query editor?
Thanks @DAX0110 , I don"t know how to set the IF in my function written in query editor (see below).
If you could correct me, you would make my day.
let PointLookUp = (address as text) => let Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&address&"?o=xml&key=sorryitsprivate")), #"Type modifié" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}), ResourceSets = #"Type changed"{0}[ResourceSets], ResourceSet = ResourceSets{0}[ResourceSet], #"Type changed1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}), Resources = #"Type modifié1"{0}[Resources], Location = Resources{0}[Location], #"Type changed2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}), Point = #"Type modifié2"{0}[Point], #"Type changed3" = Table.TransformColumnTypes(Point,{{"Latitude", type text}, {"Longitude", type text}}) in #"Type changed3" in PointLookUp
### I followed the process from this tutorial :https://sqldusty.com/2016/04/26/power-bi-and-the-bing-maps-api/
Hi @Anonymous, I tried your function with a few "wrong addresses" that Bing Maps can't handle.
The function does return an "error", but the query that calls it from a custom column will continue to run and process all the other addresses.
Do you have the code for the query that you used to call this function?
Hi @DAX0110
Here is the query code to insert the Lat,Long (please note that I cannot see the long lat columns If i don't go to the query editor, I've not found the solution to get them in the source table "Adresses" yet 😄 ).
let Source = Excel.Workbook(File.Contents("C:\Adresses.xlsx"), null, true), #"Adresses" = Source{[Item="Adresses",Kind="Sheet"]}[Data], #"Headlines" = Table.PromoteHeaders(#"Adresses_Sheet", [PromoteAllScalars=true]), #"Type changed" = Table.TransformColumnTypes(#"Headlines",{{"ID", Int64.Type},{"Adress", type text}}), #"Function called" = Table.AddColumn(#"Type modifié", "fxPointLookUp", each fxPointLookUp([Adress])), #"fxPointLookUp inserted" = Table.ExpandTableColumn(#"Function called", "fxPointLookUp", {"Latitude", "Longitude"}, {"fxPointLookUp.Latitude", "fxPointLookUp.Longitude"}) in #"fxPointLookUp inserted"
Here is a list of adresses.The first one and the last one are correct. The others are wrong.
2 RUE DES DEUX PONTS , 45017 , ORLEANS , FRANCE
2&4 RUE DE COULONGÉ , 44328 , NANTES , FRANCE
143 /147 BOULEVARD ANATOLE FRANCE , 93200 , ST DENIS , FRANCE
96 / 98 QUAI DU MARECHAL JOFFRE , 92400 , COURBEVOIE , FRANCE
, , ,
Z.I LA COUDERE , 49220 , LE LION D'ANGERS , FRANCE
Thanks for your help, it means a lot for me.
Hi @Anonymous,
Latest update: I made further changes to the function so that it handles special characters in the address such as & and /
To install my changes please follow these steps:
(1) create a new Query, and rename it to "PointLookupNew": (you also have to insert your API key)
let PointLookUpNew = (address as text) => let key = "<insert your API key here>", address2 = Uri.EscapeDataString(address), Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations?q="& address2 &"&o=xml&key=" &key)), #"Type changed" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}), ResourceSets = #"Type changed"{0}[ResourceSets], ResourceSet = ResourceSets{0}[ResourceSet], #"Type changed1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}), Resources = #"Type changed1"{0}[Resources], Location = Resources{0}[Location], #"Type changed2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}), Point = #"Type changed2"{0}[Point], #"Type changed3" = Table.TransformColumnTypes(Point,{{"Latitude", type text}, {"Longitude", type text}}), exec = try #"Type changed3" in if exec[HasError] then #table( {"Latitude","Longitude"}, {{"error","error"}} ) else exec[Value] in PointLookUpNew
(2) call this new function in the query:
let Source = Excel.Workbook(File.Contents("C:\Adresses.xlsx"), null, true), #"Adresses" = Source{[Item="Adresses",Kind="Sheet"]}[Data], #"Headlines" = Table.PromoteHeaders(#"Adresses_Sheet", [PromoteAllScalars=true]), #"Type changed" = Table.TransformColumnTypes(#"Headlines",{{"ID", Int64.Type},{"Adress", type text}}), #"Function called" = Table.AddColumn(#"Type modifié", "fxPointLookUp", each PointLookUpNew([Adress])), #"fxPointLookUp inserted" = Table.ExpandTableColumn(#"Function called", "fxPointLookUp", {"Latitude", "Longitude"}, {"fxPointLookUp.Latitude", "fxPointLookUp.Longitude"}) in #"fxPointLookUp inserted"
You will find that many of the invalid addresses (that contained special characters) now become valid.
In addition, the query will process all addresses in your list, and display the word "error" when an address truely cannot be geocoded.
Awww thank you so much ! You made my day !
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |