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

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

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.

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 protected]

 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

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