Find out latitude and longitude from postal code

[Praveen806]

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"

[Sahir_Maharaj]

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.