Forum Discussion

Praveen806's avatar
Praveen806
New Member
2 years ago

Find out latitude and longitude from postal code

I am trying to populate latitude and longitude against a list of pincodes in excel. Trying with below code but giving error.   latlong is the name of table pincode is the name of table header   ...
Need Help
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
2 years ago

Hello Praveen806,

 

Can you please try this approach:

let
    Source = Excel.CurrentWorkbook(){[Name="latlong"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pincode", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let
            SourceJson = Json.Document(Web.Contents("https://nominatim.openstreetmap.org/search?format=json&q=" & [pincode])),
            FirstResult = if List.Count(SourceJson) > 0 then SourceJson{0} else null,
            
            Latitude = if FirstResult <> null then FirstResult[lat] else null,
            Longitude = if FirstResult <> null then FirstResult[lon] else null
        in
            [lat = Latitude, lon = Longitude]
    ),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"lat", "lon"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"lat", type number}, {"lon", type number}})
in
    #"Changed Type1"

Let me know if you might require any further assistance.