Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Create google API to lookup latitude and longitute of zip code

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? 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.