Hi Ruth, This topic is great! I used until now something similar through VBA, but this is more reliable when more data is to process. Like Ahmad Hatahet I had also at the start an error with Column 1. In my case it was because My account for using the API was not set correctly yet. Now the error is gone. Every video you convince me that thanks to Power Query/BI, VBA is becoming obsolete ;-) Once again, Thanks a lot for all your videos with so much enthousiasm! Dirk
Yes! I have to change the code so it gives the google api error and not the power Bi one, the issue is find time to do it ! But glad it worked and you are becoming a power query believer :) /Ruth
Awesome! I built a method years ago (still used by my business unit) which uses an array function and re-parameterized Pythagoras for spherical coordinates that will itterate over 10s of thousands of GPS (lat/long) reference points and return the closest point with straightline distances. :) happy to provide that if its of interest
Awesome tutorial Ruth!!! One question I had was what if the use case for defining "to" and "from" are located in the same location variable? in your scenario each row has a two and from lat, long points. The scenario I am trying to solve for is where there is only one variable that has lat and long points and you need to use two variables to define what is "to" and what is "from" destination points. Example ( From destination =( (Location type = "public school A") & "location") To destination = ((Location type = "Subway B") & "location"). Thought I see if you had a way to code. Again, Awesome tutorial!!!
If I have a list of 60 different places in Power BI, would this be able to use this to create the distances to the other 59 places and then distances to all other places 60x59x58x57 etc?
Many thanks for your video! Is it possible to compare two list (tables) with coordinates lat, long and to find minimum (or any other specific parameters like less 1 km etc) distance between? Thanks for feedback
Hi Ruth, Thanks for all useful videos you do. As a beginner, i tried to download you pbix template clicking on your link. It did not work. I got "unable to down load file" message. Is there a specific trick to fix this? Regards
+DataSpinners Hi! It is this one: Flow Map store.office.com/en-us/app.aspx?assetid=WA104380901&sourcecorrid=df5b5ee8-a8cc-4add-a24c-dab978c53752&searchapppos=19&ui=en-US&rs=en-US&ad=US&appredirect=false /Ruth
Hi , Can we do the same in Power Query . Can you elaborate how the Distance function was created. I have the Lat & Long for all sites & i need to compute the distance amongst them
Thanks for the video. Can you also show how to display these routes on the map? right now they are just lines but how can I show the routes on map in power bi?
hello! Thanks for these helpful videos. Is there a way to show the actual routes of multiple Origins & Destinations on a map (Google or other) instead of just getting the distance & time values between them? Thanks!
+Marcos Mercado Hi Marcos!! Have you tried this one? store.office.com/en-us/app.aspx?assetid=WA104380985&sourcecorrid=b88d2b2a-82f7-425c-87aa-bbd460aa43ae&searchapppos=24&ui=en-US&rs=en-US&ad=US&appredirect=false I haven't tried it myself but it seems it should do what you are asking for. Let me know if it works ! /Ruth
Seria possível colocar o ponto inicial e o final em um tipo de filtro direto no painel e assim apresentar a rota no mapa? Obrigado pelas dicas aprendendo muito
+RRR program Hi! I have dear friends in India, but unfortunately I haven't been able to visit them there yet. I am planing a trip for next year to Gujarat where they live. I am really looking forward to it :) I guess my subconscious betrayed me when I chose the cities for the video!! Where in India do you come from? /Ruth
Its Hyderabadddd.... land of Biryani :D.. Advance welcome to India.... Do visit in winter .. probably anywhere between Nov to Feb..... Else there would be soaring heat.. Don't miss Rajasthan...
+RRR program Yes, they told me that too 😂! November is a perfect time to go as it is so dark and cold in Sweden and if I am lucky I might be able to attend a wedding, perfect opportunity to embrace Indian culture! Have to check where you live :) /Ruth
Hi Curbal, thanks so much for this solution! It's really awesome! Is there a way we can edit the function within Power Query to return the distance in metres, as opposed to text? e.g. "1000" instead of "1 km"
Thank you, Ruth! Can you help me with the access to Download Center? I'm trying do get the PBIX file here but apparently the Download Center isnt available.
Hi Thiago, i just checked and it is live. Can you check with another Wi-Fi or private computer? Cloud services are often blocked by companies. Let me know! -Ruth
Looks really good ! thanks for this. How can I access the .pbit file? the link in the description gives an error. Many thanks again for sharing this video !
As soon as I use the API call in my code, it works perfectly in the editor, but as soon as I publish, I am unable to schedule a refresh. I each time need to refresh on my local and republish. Does anyone know why this is?
i have some data related to long and lat for finding the distance and i had created parameters like above video but i am unable to create function as above it showing the all the table when i use invoke function....can you please help me ....i will send the excel sheet with lat and long need to find the distance
It could be something wrong in the URL that is sent to google API. You need to check everything very careful, I got that error all the time in the beginning. For more personalized help, please contact the Power bi community and follow this steps: m.ruclips.net/video/oXFOebuvRz0/видео.html /Ruth
followed each step several times. Same result, most of the google screens are now updated but I would assume the result would be the same. I will try something else.@@CurbalEN
Hi Richard, I checked to make sure the code is still ok and it works with my API key. I modified the function a bit so you can get the error message from google API and hopefully that will help you troubleshoot. Download the file again #34. /Ruth
Thank you for the Custom Function Distance calculations!
+ExcelIsFun They are neat right? :)
/Ruth
Hi Ruth, This topic is great! I used until now something similar through VBA, but this is more reliable when more data is to process.
Like Ahmad Hatahet I had also at the start an error with Column 1. In my case it was because My account for using the API was not set correctly yet. Now the error is gone.
Every video you convince me that thanks to Power Query/BI, VBA is becoming obsolete ;-)
Once again, Thanks a lot for all your videos with so much enthousiasm! Dirk
Yes! I have to change the code so it gives the google api error and not the power Bi one, the issue is find time to do it ! But glad it worked and you are becoming a power query believer :)
/Ruth
Awesome! I built a method years ago (still used by my business unit) which uses an array function and re-parameterized Pythagoras for spherical coordinates that will itterate over 10s of thousands of GPS (lat/long) reference points and return the closest point with straightline distances. :) happy to provide that if its of interest
+Collin Taylor That is amazing Collin! :)
/Ruth
Thank You Very Much for taking the time out of your busy schedule and making this awesome tutorial. Greatly appreciated.
+rajan77 The pleasure is all mine, happy to help. :)
/Ruth
Thanks Ruth! This came up as a question at work today, and I thought this sounded like a job for Power BI (Query) :) Thanks!
That is really cool - Yes please, a more detailed video would be great. Thank you
+Mark Blackburn I will, I did that a bit too quickly... I will fix that for next weeks video!
/Ruth
One more great video. Thank you Ruth!
+George Karoubas Thanks to you for watching it and take the time to let me know :)
/Ruth
Yes Ruth that could really help
Very nice video. Congratulations !
Thanks!
Awesome tutorial Ruth!!! One question I had was what if the use case for defining "to" and "from" are located in the same location variable? in your scenario each row has a two and from lat, long points. The scenario I am trying to solve for is where there is only one variable that has lat and long points and you need to use two variables to define what is "to" and what is "from" destination points. Example ( From destination =( (Location type = "public school A") & "location") To destination = ((Location type = "Subway B") & "location").
Thought I see if you had a way to code. Again, Awesome tutorial!!!
Not sure I follow, I am afraid...
If I have a list of 60 different places in Power BI, would this be able to use this to create the distances to the other 59 places and then distances to all other places 60x59x58x57 etc?
Many thanks for your video! Is it possible to compare two list (tables) with coordinates lat, long and to find minimum (or any other specific parameters like less 1 km etc) distance between? Thanks for feedback
Hi Ruth, Thanks for all useful videos you do. As a beginner, i tried to download you pbix template clicking on your link. It did not work. I got "unable to down load file" message. Is there a specific trick to fix this? Regards
+Thierry Colin Hi Thierry! My server is down at the moment. Please send me an email here and I will email it to you : Curbal.com/Contact
Thanks Ruth - looks like you used a custom vis - which one was it?
+DataSpinners Hi! It is this one:
Flow Map
store.office.com/en-us/app.aspx?assetid=WA104380901&sourcecorrid=df5b5ee8-a8cc-4add-a24c-dab978c53752&searchapppos=19&ui=en-US&rs=en-US&ad=US&appredirect=false
/Ruth
Fantastic many thanks
+DataSpinners You welcome! :) and have a nice weekend!
/Ruth
Hi , Can we do the same in Power Query . Can you elaborate how the Distance function was created. I have the Lat & Long for all sites & i need to compute the distance amongst them
Thanks for the video. Can you also show how to display these routes on the map? right now they are just lines but how can I show the routes on map in power bi?
hello! Thanks for these helpful videos. Is there a way to show the actual routes of multiple Origins & Destinations on a map (Google or other) instead of just getting the distance & time values between them? Thanks!
+Marcos Mercado Hi Marcos!! Have you tried this one?
store.office.com/en-us/app.aspx?assetid=WA104380985&sourcecorrid=b88d2b2a-82f7-425c-87aa-bbd460aa43ae&searchapppos=24&ui=en-US&rs=en-US&ad=US&appredirect=false
I haven't tried it myself but it seems it should do what you are asking for.
Let me know if it works !
/Ruth
I believe we have and didn't work as expected, but we'll give it another try. Saludos y gracias!
+Marcos Mercado Perfect, after my vacation I will do a video on it to see what it does :)
/Ruth
I am looking for videos on DAX to calculate distance between two points using lat and long. Any help is appreciated!
Seria possível colocar o ponto inicial e o final em um tipo de filtro direto no painel e assim apresentar a rota no mapa?
Obrigado pelas dicas aprendendo muito
Good one .... So, you have chosen Mumbai and Bangalore... :D... have u ever been to India ?
+RRR program Hi!
I have dear friends in India, but unfortunately I haven't been able to visit them there yet. I am planing a trip for next year to Gujarat where they live. I am really looking forward to it :)
I guess my subconscious betrayed me when I chose the cities for the video!!
Where in India do you come from?
/Ruth
Its Hyderabadddd.... land of Biryani :D..
Advance welcome to India.... Do visit in winter .. probably anywhere between Nov to Feb..... Else there would be soaring heat.. Don't miss Rajasthan...
+RRR program Yes, they told me that too 😂! November is a perfect time to go as it is so dark and cold in Sweden and if I am lucky I might be able to attend a wedding, perfect opportunity to embrace Indian culture!
Have to check where you live :)
/Ruth
Great......Have real fun :)....
+RRR program Yeah!! 😊
/Ruth
Hi Curbal, thanks so much for this solution! It's really awesome! Is there a way we can edit the function within Power Query to return the distance in metres, as opposed to text? e.g. "1000" instead of "1 km"
Hi Aaron,
Text.AfterDelimiter([distance], " ") should do it!
Hey! Is this a function we can create ourselves? Or do we need to purchase it?
You can create it yourself:)
Amazing!
Isnt it? :)
/Ruth
Thank you, Ruth! Can you help me with the access to Download Center? I'm trying do get the PBIX file here but apparently the Download Center isnt available.
Hi Thiago, i just checked and it is live. Can you check with another Wi-Fi or private computer? Cloud services are often blocked by companies.
Let me know!
-Ruth
@@CurbalEN You're right! It works. Thank you again, Ruth.
How to add more modes for example vessels ?
Looks really good ! thanks for this. How can I access the .pbit file? the link in the description gives an error. Many thanks again for sharing this video !
Great video! Thank you Ruth, this is very helpful. However, i'm also unable to download the .pbit file :(
Check this out:
m.ruclips.net/video/NzmZcuyjWB4/видео.html
/Ruth
Fantastic
+Cloud Hound Thanks!
/Ruth
As soon as I use the API call in my code, it works perfectly in the editor, but as soon as I publish, I am unable to schedule a refresh. I each time need to refresh on my local and republish. Does anyone know why this is?
Hi,
The Link to pbix template it's broken. :(
I would be very grateful.
Hi Daniel,
Here is the new link:
gofile.me/2kEOD/BGLRw3qdv
/Ruth
¡ Thanks so much !
+Daniel Garcia Alvarez De nada ;)
/Ruth
how to create lat and long function ....can you please explain detailed????
Hi Sandeep, what do you mean exactly?
/Ruth
i have some data related to long and lat for finding the distance and i had created parameters like above video but i am unable to create function as above it showing the all the table when i use invoke function....can you please help me ....i will send the excel sheet with lat and long need to find the distance
Hi Sandeep, I am not able to provide individual support, I get too many questions. Could you please post this in the Power BI community?
Thanks!
/Ruth
Hi Ruth!!! I am not able to download the .pbit file
thanskss
Hi Igor, all download files have moved to the download center at Curbal.com
@@CurbalEN Thankssss Ruth!!!! It's amazing!!
Yey!! Glad you found it and it was useful :)))
/Ruth
Hi there! Can you upload the .pbit file again? I cannot download it
Hi, all download files have been moved to the download center at Curbal.com
/Ruth
The file doesn't seem to work anymore it is putting an error of can not find column 1 all the time
That means that something is wrong with your api key. Check that is valid.
/Ruth
I've only just got the api key tried it a few times same result @@CurbalEN
It could be something wrong in the URL that is sent to google API. You need to check everything very careful, I got that error all the time in the beginning.
For more personalized help, please contact the Power bi community and follow this steps:
m.ruclips.net/video/oXFOebuvRz0/видео.html
/Ruth
followed each step several times. Same result, most of the google screens are now updated but I would assume the result would be the same. I will try something else.@@CurbalEN
Hi Richard,
I checked to make sure the code is still ok and it works with my API key.
I modified the function a bit so you can get the error message from google API and hopefully that will help you troubleshoot.
Download the file again #34.
/Ruth
Hello l cant download the file please help
You will find it in the download center at Curbal.com
/Ruth
Hide your key @ 4:14 ;-)
+Zombie Brainz Oh dear, thanks! Better to reset it instead! Done ✅!
/Ruth