Google Maps Distance Function Help

[TGoodheart]

I'm trying to use a function that takes in a concatenated addresses, makes a google maps API call and returns the drive time/distance covered, I have had success with the formulas and can see the data in the rows, but the functions fail when I go to apply.  My original function grabbed both but kept failing with the following error.

let
    get_dist_duration = (maplocation as text) =>

let
    Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=Edison,NJ&destinations="&maplocation&"&key=MYAPIKEY&callback=initMap"), 65001),
    #"Converted to Table1" = Record.ToTable(Source),
    Value = #"Converted to Table1"{2}[Value],
    Value1 = Value{0},
    elements = Value1[elements],
    elements1 = elements{0},
    #"Converted to Table" = Record.ToTable(elements1),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Removed Columns" = Table.RemoveColumns(#"Transposed Table",{"Column3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Expanded distance" = Table.ExpandRecordColumn(#"Promoted Headers", "distance", {"text", "value"}, {"distance.text", "distance.value"}),
    #"Expanded duration" = Table.ExpandRecordColumn(#"Expanded distance", "duration", {"text", "value"}, {"duration.text", "duration.value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded duration",{"duration.value", "distance.value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"distance.text", "distance"}, {"duration.text", "duration"}})
in
    #"Renamed Columns"
in

    get_dist_duration

I changed the functions around into a separate Duration and Distance function after invoking it I'm getting an error saying the record distance isn't found, any help is much apprecaited.

let
get_distance = (maplocation as text) =>

let
    Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=Edison,NJ&destinations="&maplocation&"&key=MYAPIKEY&callback=initMap")),
    rows = Source[rows],
    rows1 = rows{0},
    elements = rows1[elements],
    elements1 = elements{0},
    distance = elements1[distance],
    #"Converted to Table" = Record.ToTable(distance),
    #"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"Name"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Bottom Rows",{{"Value", "distance(mi)"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," mi","",Replacer.ReplaceText,{"distance(mi)"})
in
    #"Replaced Value"
in 
get_distance

[v-qiuyu-msft · Accepted solution]

Hi @TGoodheart,

From your description, it seems that you want to pass dynamic destinations to in URL to generate data. Please try to use the Web data source to call Google Maps API firstly follow this to see if the distance can return. Then follow those articles to create custom function and invoke it.

Using The Invoke Custom Function Button In Power BI

Custom Functions Made Easy in Power BI Desktop

Besides, there are errors with three queries when you apply changes, I would suggest all those three queries one by one. By the way, please try to run the latest desktop version.

Best Regards,
Qiuyun Yu

[TGoodheart]

Hi @v-qiuyu-msft,

Thank you for responding. I found out that I was running up against the API call daily limit, I kept the first 2.5k rows and the queries worked.

Thank you for the help!

TG