Create google API to lookup latitude and longitute of zip code

[Anonymous]

I have a table with zip codes. I would like to lookup the latitidue and longitude using a google API (I see lot's of posts on this). I've never created an API. How can I create a google API to give me the latitude and longitude of a zip code in a table? 

[mahoneypat · Accepted solution]

Please see these steps/examples to get long lat from a zip code with the Google Geocode API

1. Follow these instructions to create a billing account (don't worry you get quite a bit of free credit, but you can track it to make sure), enable the Geocode API, and get an API key (keep this confidential).

https://developers.google.com/maps/gmp-get-started#api-key

2. Have a table with a zipcode column in text format (you have that already). 

3. Create a text parameter called MyApiKey (case sensitive) and put your new API key in it

4. Make a new blank query and paste this M code to make a function and call it fxGetLongLatFromZip

let
Source = (zipcode as text) => let
Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/geocode/json?address=" & zipcode & "&key=" & MyApiKey)),
results = Source[results],
results1 = results{0},
geometry = results1[geometry],
location = geometry[location]
in
location
in
Source

5. On your table with zipcodes, add a column by hitting the Invoke Custom Function button.  Choose your Zip column as the input.

6. Expand the returned record and keep drilling through the JSON until you find the Long and Lat values.

7. Enjoy your long lat data.

8. Mark this as the solution and please give Kudos.

Please see this example M query with a few zipcodes to test it out and see the drill steps to get to long and lat from the JSON response from the google API.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjG3NDBUitWBsIzgLGM4y0QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Zip = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Zip", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fxGetLongLatFromZip", each fxGetLongLatFromZip([Zip])),
#"Expanded fxGetLongLatFromZip" = Table.ExpandRecordColumn(#"Invoked Custom Function", "fxGetLongLatFromZip", {"lat", "lng"}, {"lat", "lng"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded fxGetLongLatFromZip",{{"lat", type number}, {"lng", type number}})
in
#"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat