Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TGoodheart
Frequent Visitor

Google Maps Distance Function Help

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.

PBIError1.png

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

 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors