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

[Anonymous]

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 question is, can I add a condition or some other trick to prevent the fucntion from running unless my distance columns are blank. On each edit of the query no matter how minor, I am forced to apply changes and the function processes the distance on all 900 plus rows.

thanks for any help

[parry2k]

@Anonymous you can use Incremental refresh techniques to not process the records that are already processed. There are few articles on how to do this, here is one such post

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@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. 

[Anonymous]

Thanks for the response, any chance of a partial refresh using a date range in the "Manage Parameters" for the query feature?

[PhilipTreacy]

Hi @Anonymous 

It would really help if you actually posted the code you are using, so we aren't guessing what it is doing.  And some sample data so we can see your data structure/column names.

What's the full error message?

Which step is generating it?

If you only want to call the function when a column does not contain null then use this.  Assuming you want to check the [Address] and [Work Location] columns for null

each if [Address] <> null and [Work Location] <> null then fnDistince([Address], [Work Location]) else null

Regards

Phil

[Anonymous]

I tried this from mahoneypat's suggestion

each if [Distance] = null then fxMilesDuration([Address],[Work Location]) else [Distance]

and this error

Expression.Error: The field 'Distance' of the record wasn't found.
Details:
    Department Org Code=9000
    Department Name=MyDept
    ID=001901
    Classification=INFORMATION TECH
    Work Schedule=5-8-40 MTWTF 8
    Report Date=12/28/2020
    Name=AnyName
    Email=Any.Nname@MyDept.com

 I also tried using "Distance" instead of [Distance] and got this

Expression.Error: We cannot convert the value "Distance" to type Table.
Details:
    Value=Distance
    Type=[Type]

Here is the function

let 
    Route = (StartAddress as text, DestinationAddres as text) =>
let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&distanceUnit=mi&wp.0="&StartAddress&"&wp.1="&DestinationAddres &"&avoid=minimizeTolls&key=MyBingApiKey
")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    ResourceSets = #"Changed Type"{0}[ResourceSets],
    ResourceSet = ResourceSets{0}[ResourceSet],
#"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
    Resources = #"Changed Type1"{0}[Resources],
    Route = Resources{0}[Route],
#"Changed Type2" = Table.TransformColumnTypes(Route,{{"Id", type text}, {"DistanceUnit", type text}, {"DurationUnit", type text}, {"TravelDistance", type number}, {"TravelDuration", Int64.Type}, {"TravelDurationTraffic", Int64.Type}, {"TrafficDataUsed", type text}, {"TrafficCongestion", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Id", "BoundingBox", "DistanceUnit", "DurationUnit", "TrafficDataUsed", "TrafficCongestion", "RouteLeg"})
in
#"Removed Columns"
in 
    Route

My steps

let
    Source = Excel.Workbook(File.Contents("C:\Users\user\Documents\My Docs\request\tEmployeeHr.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Department Org Code", type text}, {"Department Name", type text}, {"ID", type text}, {"Classification", type text}, {"Work Schedule", type text}, {"Report Date", type date}, {"Name", type text}, {"Email", type text}, {"Dept ID", Int64.Type}, {"Descr", type text}, {"SUP NAME", type text}, {"CampusCode", type text}, {"CampusAddress", type text}, {"CampusCity", type text}, {"CampusState", type text}, {"CampusZip", Int64.Type}, {"Address 1", type text}, {"Address 2", type text}, {"City", type text}, {"State", type text}, {"Postal", Int64.Type}, {"FullName", type text}, {"FName", type text}, {"LName", type text}, {"Active", type logical}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Postal", type text}, {"CampusZip", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "MergedFullAddress", each Text.Combine({[Address 1], [City], [State], Text.From([Postal], "en-US")}, ","), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "MergedCityStateZip", each Text.Combine({[City], [State], Text.From([Postal], "en-US")}, ","), type text),
    #"Inserted Merged Column2" = Table.AddColumn(#"Inserted Merged Column1", "Work Location", each Text.Combine({[CampusAddress], [CampusCity], [CampusState], Text.From([CampusZip], "en-US")}, ","), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column2", "Address", each if [Address 1] = [Postal] then [MergedCityStateZip] else [MergedFullAddress]),
    #"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"

 I don't have a lot of experience but it seems I need to add a step to expand the distance columns before the condition then apply the function when Distance is null. Thanks

[Anonymous]

Ok let me get what you asked for, but the addresses will never be null, it's the expanded columns (TravelDistance, TravelDuration)  that will be null with new records. I am trying to prevent the refresh of existing records.

[mahoneypat]

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]

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

[Anonymous]

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"