Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Calculate distnce where source and target lat long are in same table.
I want to add coumn with distance between source an targer for each row.
I need help with power query if possible.
S is for Source
T is for Target
S_Cell | T_Cell | T_SiteId | S_Lat | S_Long | T_Lat | T_Long |
London | NewYork | NJ06941A | 40.80527878 | -74.07833862 | 40.80527878 | -74.07833862 |
Solved! Go to Solution.
@PSB I don't know of a Power Query solution, but the DAX solution is here: Going the Distance - Microsoft Power BI Community
Hi @PSB
Download example file with working query
Here's the query code
let
BingMapsKey = "ENTER YOUR KEY HERE",
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTzMjCzNDF0NDZU0lHyRuHBOECmiaGehYGpkbmFuQWQp2turGdgbmFsbGFmBJI0QJU0QUjG6iBZYYRihSG6FbhNId4KQxQrjGhhBdhQR5wBRX0raBJQdLeCdnGBO9FSP0VRLaBiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S_Cell = _t, T_Cell = _t, T_SiteId = _t, S_Lat = _t, S_Long = _t, T_Lat = _t, T_Long = _t]),
#"Added Custom" = Table.AddColumn(Source, "Distance", each Json.Document(Web.Contents("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" & [S_Lat] & "," & [S_Long] & "&destinations=" & [T_Lat] & "," & [T_Long] & "&travelMode=driving&key=" & BingMapsKey))),
#"Expanded Distance" = Table.ExpandRecordColumn(#"Added Custom", "Distance", {"resourceSets"}, {"Distance.resourceSets"}),
#"Expanded Distance.resourceSets" = Table.ExpandListColumn(#"Expanded Distance", "Distance.resourceSets"),
#"Expanded Distance.resourceSets1" = Table.ExpandRecordColumn(#"Expanded Distance.resourceSets", "Distance.resourceSets", {"resources"}, {"Distance.resourceSets.resources"}),
#"Expanded Distance.resourceSets.resources" = Table.ExpandListColumn(#"Expanded Distance.resourceSets1", "Distance.resourceSets.resources"),
#"Expanded Distance.resourceSets.resources1" = Table.ExpandRecordColumn(#"Expanded Distance.resourceSets.resources", "Distance.resourceSets.resources", {"results"}, {"Distance.resourceSets.resources.results"}),
#"Expanded Distance.resourceSets.resources.results" = Table.ExpandListColumn(#"Expanded Distance.resourceSets.resources1", "Distance.resourceSets.resources.results"),
#"Expanded Distance.resourceSets.resources.results1" = Table.ExpandRecordColumn(#"Expanded Distance.resourceSets.resources.results", "Distance.resourceSets.resources.results", {"travelDistance"}, {"travelDistance"})
in
#"Expanded Distance.resourceSets.resources.results1"
You can use the BING Maps API (or another API) to get these distances.
The first thing you will need to do is sign up for a Bing maps API Key
Getting a Bing Maps Key - Bing Maps | Microsoft Learn
Then you can issues HTTP GET requests for each pair of co-ordinates
Calculate a Distance Matrix - Bing Maps | Microsoft Learn
The request/query looks like this
https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=40.80527878,-74.07833862&destinat...{Bing maps API Key}
This returns JSON which can be easily parsed
{"authenticationResultCode":"ValidCredentials","brandLogoUri":"http:\/\/dev.virtualearth.net\/Branding\/logo_powered_by.png","copyright":"Copyright © 2022 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.","resourceSets":[{"estimatedTotal":1,"resources":[{"__type":"DistanceMatrix:http:\/\/schemas.microsoft.com\/search\/local\/ws\/rest\/v1","destinations":[{"latitude":40.80527878,"longitude":-74.07833862}],"origins":[{"latitude":41.80527878,"longitude":-73.07833862}],"results":[{"destinationIndex":0,"originIndex":0,"totalWalkDuration":0,"travelDistance":185.968,"travelDuration":116.6667}]}]}],"statusCode":200,"statusDescription":"OK","traceId":"91c183c71ee046659f3ad5c1494ff45a|PUS0004C75|0.0.0.0|PUS0005C77"}
to retrieve the distance.
NOTE: All your pairs of co-ords are the same so there's 0 distance betwen them.
Regards
Phil
Proud to be a Super User!
@PSB Don't use LOOKUPVALUE then, just use a column reference (with any aggregator).
Hi @PSB
Download example file with working query
Here's the query code
let
BingMapsKey = "ENTER YOUR KEY HERE",
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTzMjCzNDF0NDZU0lHyRuHBOECmiaGehYGpkbmFuQWQp2turGdgbmFsbGFmBJI0QJU0QUjG6iBZYYRihSG6FbhNId4KQxQrjGhhBdhQR5wBRX0raBJQdLeCdnGBO9FSP0VRLaBiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S_Cell = _t, T_Cell = _t, T_SiteId = _t, S_Lat = _t, S_Long = _t, T_Lat = _t, T_Long = _t]),
#"Added Custom" = Table.AddColumn(Source, "Distance", each Json.Document(Web.Contents("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" & [S_Lat] & "," & [S_Long] & "&destinations=" & [T_Lat] & "," & [T_Long] & "&travelMode=driving&key=" & BingMapsKey))),
#"Expanded Distance" = Table.ExpandRecordColumn(#"Added Custom", "Distance", {"resourceSets"}, {"Distance.resourceSets"}),
#"Expanded Distance.resourceSets" = Table.ExpandListColumn(#"Expanded Distance", "Distance.resourceSets"),
#"Expanded Distance.resourceSets1" = Table.ExpandRecordColumn(#"Expanded Distance.resourceSets", "Distance.resourceSets", {"resources"}, {"Distance.resourceSets.resources"}),
#"Expanded Distance.resourceSets.resources" = Table.ExpandListColumn(#"Expanded Distance.resourceSets1", "Distance.resourceSets.resources"),
#"Expanded Distance.resourceSets.resources1" = Table.ExpandRecordColumn(#"Expanded Distance.resourceSets.resources", "Distance.resourceSets.resources", {"results"}, {"Distance.resourceSets.resources.results"}),
#"Expanded Distance.resourceSets.resources.results" = Table.ExpandListColumn(#"Expanded Distance.resourceSets.resources1", "Distance.resourceSets.resources.results"),
#"Expanded Distance.resourceSets.resources.results1" = Table.ExpandRecordColumn(#"Expanded Distance.resourceSets.resources.results", "Distance.resourceSets.resources.results", {"travelDistance"}, {"travelDistance"})
in
#"Expanded Distance.resourceSets.resources.results1"
You can use the BING Maps API (or another API) to get these distances.
The first thing you will need to do is sign up for a Bing maps API Key
Getting a Bing Maps Key - Bing Maps | Microsoft Learn
Then you can issues HTTP GET requests for each pair of co-ordinates
Calculate a Distance Matrix - Bing Maps | Microsoft Learn
The request/query looks like this
https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=40.80527878,-74.07833862&destinat...{Bing maps API Key}
This returns JSON which can be easily parsed
{"authenticationResultCode":"ValidCredentials","brandLogoUri":"http:\/\/dev.virtualearth.net\/Branding\/logo_powered_by.png","copyright":"Copyright © 2022 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.","resourceSets":[{"estimatedTotal":1,"resources":[{"__type":"DistanceMatrix:http:\/\/schemas.microsoft.com\/search\/local\/ws\/rest\/v1","destinations":[{"latitude":40.80527878,"longitude":-74.07833862}],"origins":[{"latitude":41.80527878,"longitude":-73.07833862}],"results":[{"destinationIndex":0,"originIndex":0,"totalWalkDuration":0,"travelDistance":185.968,"travelDuration":116.6667}]}]}],"statusCode":200,"statusDescription":"OK","traceId":"91c183c71ee046659f3ad5c1494ff45a|PUS0004C75|0.0.0.0|PUS0005C77"}
to retrieve the distance.
NOTE: All your pairs of co-ords are the same so there's 0 distance betwen them.
Regards
Phil
Proud to be a Super User!
can you help modify below query for disatance query.
I have lat long in different table and "From" and "To" filed is in different table. I want to calculate distance by looking up values of lat and long from different table.
I've placed query you provided and is helpful when source and taget "lat" and "long" is in same table.
From | To | Distance (Miles) |
Berlin | Tokyo | ? |
Mumbai | New York | ? |
London | Paris | ? |
City | Lat | Long |
Berlin | 39.58305 | -74.78388977 |
Mumbai | 39.55434 | -74.74279785 |
London | 39.51902 | -74.69287872 |
Tokyo | 39.48972 | -74.59777832 |
New York | 39.45287 | -74.63844299 |
Paris | 39.43 | -74.57861328 |
---------------
query you provided earlier
-------------
I am getting this error, while adding this step in my existing query. How can this be resolved?
Formula.Firewall: Query 'All Neighbor Relations' (step 'Custom1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
This Solution wolked. Thanks
@PSB I don't know of a Power Query solution, but the DAX solution is here: Going the Distance - Microsoft Power BI Community
This example has values in two different rows. Mine is in same row.
Due to this I'm getting only zero distances.
@PSB Don't use LOOKUPVALUE then, just use a column reference (with any aggregator).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |