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

 

let
Source = Excel.CurrentWorkbook(){[Name="latlong"]}[Content], 
#"Changed Type" = Table.TransformColumnTypes(Source,{{"pincode", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://nominatim.openstreetmap.org/search?format=json&q=" & Text.From([pincode])))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"lat", "lon"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"lat", type number}, {"lon", type number}})
in
#"Changed Type1"

Need Help

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super 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.