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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Eric_Zhang
Employee
Employee

@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
Not applicable

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
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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