Power Query – get the distance between two places using Google API
If you have in Power Query two places with GPS coordinates, you can see how far are they - distance and time.
It works in both Power Query in Excel as well as in Power Query in Power BI.
Get the API key
You need a key from Google (or similar provider). Let´s see this:
https://developers.google.com/maps/documentation/javascript/get-api-key
It´s free in reasonable numbers of addresses.
Power Query
You need a table with GPS coordinates for both - origin and destination. In text datatype, using decimal commas.
Then add custom column with this code:
- =Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json?key=HERE_IS_YOUR_KEY="&[Origin Latitude]&","&[Origin Longitude]&"&destinations="&[Destination Latitude]&","&[Destination Longitude]))
Then use the double arrows and drill it to info you need.
You will get this: