Using Bing Maps API to Geocode addresses with Power Query
Vložit
- čas přidán 29. 08. 2020
- This is part 2 of our "how to geocode" demonstrations. In Part 1 we demonstrated how to Geocode and address list with the Google Maps API. In this demonstration, we show how to use Bing Maps Geolocation API.
Part1. Geocoding with Google Maps API can be found here: • Create a Power Query G...
An Executive Summary provides a comparison of the two. Our opinion is Google is the fastest by far but, Bing returns more information and may be easier to validate the return information.
As with Google, Bing requires you to set up an account to get an API Key. You can set up your account here.
www.bingmapsportal.com/
Bings API documentation and templates are here.
docs.microsoft.com/en-us/bing...
Table of Contents
Executive Summary 0:26
Introduction 1:21
Bing Sample Query: 3:40
Power Query Data Extraction Steps 6:00
Creating the Power Query Function 8:12
M Code for multi-parameter function 9:15
Invoke the Power Query Function 10:56
Running the Query 14:24
Conclusion 14:35
Please, Let me know if you have any questions or suggestions in the comments below. - Věda a technologie
Never mind. Figured it out. I missed some coding. Thanks so much for creating this video.
My custom query loads, but it returns Null values for formatted address, Confidence, Lat and Long?
Any idea what I am doing wrong?
How do you transform in the advanced editor portion using the United States URL?
Ok thanks
Hey im only using locality as the Parameter however if the locaiton is not found i get an error for those rows
Expression.Error: We cannot convert the value "" to type Table.
Details:
Value=
Type=[Type]
any idea how to tackle this to just say not found. Thanks in advance
I also had the same issue. However, I knew all the addresses I had were valid and so I had to work out why the API wasn't recognising them. I found out that certain punctuation in an address or use of some abbreviations such as Cnr instead of Corner created the error. So my solution was to clean the datasource before running the API. Having said that, wouldn't it be possible for the errors to excluded from the API returns by placing a filter to exclude those rows with errors?
@@marketgrunt hey yes I did something on the similar lines as a workaround while we get our master data cleaned up. Thanks so much for the quick response though.
Still trying to figure out distance between 2 international points. Any tips ?
@@fauzanpettiwala2596 There's a lot of info available for getting distance between Lat/Long co-ordinates. I ended up using the Power Query method because it was easier to code the DAX to select points in a radius of a position. This link will take you to a site that describes the method. www.girlswithpowertools.com/2014/05/distance/. Or, here is the Havershine Calculation of distance between points written for DAX.
Havershine Calc of distance between points =
Var Pi = 3.1415
Var A1 = [Ref Lat]* Pi/180
Var A2 = [Ref Long]* Pi/180
Var B1 = [To Lat] * Pi/180
Var B2 = [To Long]* Pi/180
Var Dlon = Abs(A2 - B2)
Var DLat = Abs(A1 - B1)
Var R = 6371
Return
IF (
A1 = BLANK()|| A2 = BLANK()|| B1 = BLANK() || B2 = BLANK(),
BLANK(),
ACOS( SIN(A1)*SIN(B1) + COS(A1) * COS(B1) * COS(Dlon) ) * R
)
When I run the query with fewer (less than 10) addresses then it gives me results. When I try to geocode 50+ addresses at a time then it throws credential error. Any idea? I am using url for query by address.
It's difficult to say. It could be limitations coming from Bing or it may be some complexity issue with the address being passed to Bing. This tutorial was done 3 years ago and things have changed quite a bit. Have you tried geo-coding through Azure instead of Bing?
How to create Bing Maps API to Geocode addresses for Salesforce
Hi Gautam, I'm not proficient in the Saleforce CRM. I assumed addresses in Salesforce were auto-geolocated because it has a mapping capability.