Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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 

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

 

Any suggestions/experiences on this subject is appreciated.

Need Help
Tips and Tricks

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

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