Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |