Extract addresses not plotted on map

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.