Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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
Since Power Query is integrated in Power BI, so that seems a promising approach.
I hope my findings can give you a good start.
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)))))
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |