Using Google Matrix API to calculate distance between locations in my dataset

[donnellyk]

I have a dataset returning 77,000 records, each with both Buyer & Seller locations.
The Seller location is always in the UK
I'm trying to use the Google Matrix API to calculate distance between buyer & seller locations.
If the buyer location is in the UK, I want to calculate the distance between buyer & seller locations.
If the buyer locaion is not in the UK, I just want to calculate the distance between the seller location & a set UK location, e.g Southampton Shipping Port

I've created a function, as below, which returns distances fine
But when I Invoke this custom function as a column, I get problems:

Non UK buyer addresses return "null" as distance cannot be calculated by the API & I'm not sure how to just get the distance to southhampson for those
& also I get "Load Query1

OLE DB or ODBC error: [Expression.Error] The column 'Column1' of the table wasn't found.."

= (Origin as text, Destination as text)=>
let
    Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json?destinations="&Destination&"&origins="&Origin&"&units=imperial&keyMYAPIKEY")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"destination_addresses", type any}, {"origin_addresses", type any}, {"rows", type any}, {"status", type text}}),
    rows = #"Changed Type"{0}[rows],
    #"Converted to Table1" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"elements"}, {"Column1.elements"}),
    #"Expanded Column1.elements" = Table.ExpandListColumn(#"Expanded Column1", "Column1.elements"),
    #"Expanded Column1.elements1" = Table.ExpandRecordColumn(#"Expanded Column1.elements", "Column1.elements", {"distance"}, {"Column1.elements.distance"}),
    #"Expanded Column1.elements.distance" = Table.ExpandRecordColumn(#"Expanded Column1.elements1", "Column1.elements.distance", {"value"}, {"Column1.elements.distance.value"})
in
    #"Expanded Column1.elements.distance"

[donnellyk · Accepted solution]

Thanks for your feedback, it helped point me in the right direction.
Basically, I ammended my SQL query so that if it was a non UK address, I returned the address of the UK port of entry & then the google matrix api was able to calculate all the distances for me

[v-menakakota]

Hi @donnellyk ,

Glad to know that your issue is now resolved. Thanks for sharing your approach, and please continue to use the Fabric Community for any future questions or support.

Best Regards, 
Community Support Team

[kushanNa]

Hi @donnellyk 

From what I understand, your function works fine with UK buyers but has an issue with non-UK buyers. If that is the case, check it in an API testing tool like Postman and see what output you get when you input a UK buyer’s location and a non-UK buyer’s location. If the API returns null in that case for No UK buyer's, then this is likely a Google Maps-side issue.

[GeraldGEmerick]

@donnellyk You are getting the Column1 error because it is returning an empty table.