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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.