Power Query help to calculate distance

[PSB]

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

[Greg_Deckler · Accepted solution]

@PSB I don't know of a Power Query solution, but the DAX solution is here: Going the Distance - Microsoft Power BI Community

[PhilipTreacy · Accepted solution]

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

[PhilipTreacy · Accepted solution]

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

[PSB]

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

-------------

c =

    VAR __FromCity = SELECTEDVALUE(Main[From])

    VAR __ToCity = SELECTEDVALUE(Main[To])

    VAR __FromLat = LOOKUPVALUE(Lat_Long[Lat],__FromCity)

    VAR __ToLat = LOOKUPVALUE(Lat_Long[Lat],__ToCity)

    VAR __FromLong = LOOKUPVALUE(Lat_Long[Long],__FromCity)

    VAR __ToLong = LOOKUPVALUE(Lat_Long[Long],__ToCity)

    VAR __distanceLong = RADIANS(__ToLong - __FromLong)

    VAR __distanceLat = RADIANS(__ToLat - __FromLat)

    VAR __a = (SIN(__distanceLat/2))^2 + COS(RADIANS(__FromLat)) * COS(RADIANS(__ToLat)) * SIN((__distanceLong/2))^2

    VAR __y = SQRT(__a)

    VAR __x = SQRT(1 - __a)

    VAR __atan2 =

        SWITCH(

            TRUE(),

            __x > 0, ATAN(__y/__x),

            __x < 0 && __y >= 0, ATAN(__y/__x) + PI(),

            __x < 0 && __y < 0, ATAN(__y/__x) - PI(),

            __x = 0 && __y > 0, PI()/2,

            __x = 0 && __y < 0, PI()/2 * (0-1),

            BLANK()

        )

    VAR __c = 2 * __atan2

RETURN

    __c

[PSB]

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.

[PSB]

This Solution wolked. Thanks

[Greg_Deckler · Accepted solution]

@PSB I don't know of a Power Query solution, but the DAX solution is here: Going the Distance - Microsoft Power BI Community

[PSB]

This example has values in two different rows. Mine is in same row.

Due to this I'm getting only zero distances.

[Greg_Deckler · Accepted solution]

@PSB Don't use LOOKUPVALUE then, just use a column reference (with any aggregator).