cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anais_G Frequent Visitor
Frequent Visitor

Geolocation Search

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 Sans titre.png

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

 

Any suggestions/experiences on this subject is appreciated.

3 REPLIES 3
Super User
Super User

Re: Geolocation Search

@Anais_G - 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

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator Eric_Zhang
Moderator

Re: Geolocation Search

@Anais_G

 

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.

Anais_G Frequent Visitor
Frequent Visitor

Re: Geolocation Search

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)))))

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 3,224 guests
Please welcome our newest community members: