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
Anonymous
Not applicable

Extract addresses not plotted on map

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

 

1 ACCEPTED 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.

 

 

geocode.png

 

 

 

 

 

View solution in original post

6 REPLIES 6
DAX0110
Resolver V
Resolver V

Hi @Anonymous, have you tried catching errors with the "try" statement in the query editor?

Anonymous
Not applicable

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?

 

 

Anonymous
Not applicable

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.

 

 

geocode.png

 

 

 

 

 

Anonymous
Not applicable

Awww thank you so much ! You made my day !

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.