Forum Discussion
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...
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.
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 nullRegards
Phil
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