Geolocation Search

[Anonymous]

Hi,

I have venues locations (Lat, Long & Address) located on a map in Power BI.

I want my user to fill out an address and a radius to filter only on venue located in the target area.

Is there a way to get Lat & Long with an address ?

Any suggestions on this point ?

At this time, i created measures "Long", "Lat" where i store a target location. Then, i added a new column "distance" which calculates the distance between the point (Lat,Long) and the point of the current line.

I display this value on my dashboard 

Is there a way to change this measures directly from the dashboard ? 

Any suggestions/experiences on this subject is appreciated.

[Eric_Zhang]

@Anonymous

Is there a way to get Lat & Long with an address ?

As Smoupre suggests, Bing Maps services are good choice. I was searching and would like to share some findings interesting.

Web Services And POST Requests In Power Query

Find a Location by Address

Since Power Query is integrated in Power BI, so that seems a promising approach.

I hope my findings can give you a good start.

[Anonymous]

Hi,

Thanks for your help !

I import an excel file of venues with coordinates (Lat & Long) in Power BI and my aim is to calculate the distance between a user and each venue.

User address is in another table (3 fields : address / lat / long). I want to change from Power BI manually the user address and to get automatically its lat & long to calculte the distance.

I wrote a Power Query request to get automatically coordinates of the address and I imported it into Power BI.

It works but for changing user address i need to edit in Excel my table and refresh it in Power BI.

I tried to change address value from Power BI but it updated my Power Query code ... 

Is there a way to update manually table value without changing Power Query request from Power BI ?

My Power Query Code (it mays help people) :

let
    Source = Excel.Workbook(File.Contents("FILE"), null, true),
    Address = Source{0}[Address_user],
    Address2 = Source{0}[Address_user],
    GetAddress = Web.Contents("http://maps.google.com/maps/api/geocode/json?address=" & Address),
    GetAddress2 = Web.Contents("http://maps.google.com/maps/api/geocode/json?address=" & Address2),
    ImportedJSON = Json.Document(GetAddress),   
    ImportedJSON2 = Json.Document(GetAddress2),  
    results = ImportedJSON[results],
    results2 = ImportedJSON2[results],
    Latitude1 = results{0},
    Latitude2 = results2{0},
    geometry = Latitude1[geometry],
    geometry2 = Latitude2[geometry],
    location = geometry[location][lat],
    longitude = geometry2[location][lng], 
    Tableau2_Table = Source{[Item="Tableau2",Kind="Table"]}[Data],
    #"Type modifié" = Table.TransformColumnTypes(Tableau2_Table,{{"Address_user", type text}, {"Latitude_user", type number}, {"Longitude_user", type number}}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Type modifié",Source{0}[Latitude_user],location,Replacer.ReplaceValue,{"Latitude_user"}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Valeur remplacée1",Source{0}[Longitude_user],longitude,Replacer.ReplaceValue,{"Longitude_user"})   
in
    #"Valeur remplacée2"

I added a column distance in my venue table with this formula in Power BI :

Distance_km = 6371*((2*ASIN(SQRT((SIN((RADIANS(Tableau2[Lat_user])-RADIANS(VALUE([Coord_X])))/2)^2)+COS(RADIANS(Tableau2[Lat_user]))*COS(RADIANS(VALUE([Coord_X])))*(SIN((RADIANS(Tableau2[Long_user])-RADIANS(VALUE([Coord_Y])))/2)^2)))))

[Greg_Deckler]

@Anonymous - There is no facility within Power BI that I know of to do that, you would have to use a service like Bing Maps.

https://www.microsoft.com/maps/developer-resources.aspx