Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Prevent Bing map API function from running/refreshing when the query is edited.

hello all, I created a function that calls the Bing map web service, it works great and gives me distance traveled and duration between my start and finish locations, however it is a bit slow.   My...
Need Help
Tips and Tricks
  • parry2k's avatar
    parry2k
    5 years ago

    Anonymous you cannot avoid the full refresh, as soon as you refresh, it is going to refresh and make that function call for each row. 

     

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

In the step where you invoke your custom function, you can wrap it in an if..then..else, where it checks if the Distance column = null.  If so, run the function and, if not, return the Distance column value.

 

Instead of "... each fnDistince([City1], [City2]) ...", try

"... each if [Distance] = null then fnDistince([City1], [City2]) else [Distance] ..."

 

Regards,

Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you, that looks like it will do the trick, I will try it and post the result and accept your solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Here are my current steps, I tried inserting the condition with no luck. The error indicated that Distance could not be converted?

      #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Distance", each fxMilesDuration([Address],[Work Location])),
      #"Expanded Distance" = Table.ExpandTableColumn(#"Added Custom", "Distance", {"TravelDistance", "TravelDuration"}, {"Distance.TravelDistance", "Distance.TravelDuration"})
      in
      #"Expanded Distance"