Power Query help to calculate distance

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&destinations=40.80527878,-74.07833862&travelMode=driving&key={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 Don't use LOOKUPVALUE then, just use a column reference (with any aggregator).