Forum Discussion
cameroon
2 years agoFrequent Visitor
Formula.Firewall error when invoking custom funtion that references another data source
I have built a query in Power Query that retrieves a list of buildings from an api call, filters it so that only the buildings related to a selected client are left, then is meant to retrieve the ind...
lbendlin
Super User
2 years agoPlease follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
cameroon
2 years agoFrequent Visitor
Made the changes to the source as suggested:
Updated Query
let
Source = Json.Document(Web.Contents("https://apiv2.***********.com", [RelativePath="v2.0/customers", Headers=[Authorization="*************************************************"]])),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"customerID", "name", "buildings"}, {"client.customerID", "client.name", "client.buildings"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Column1", each ([client.name] = enteredSelectedClient)),
#"Expanded client.buildings" = Table.ExpandListColumn(#"Filtered Rows1", "client.buildings"),
#"Expanded client.buildings1" = Table.ExpandRecordColumn(#"Expanded client.buildings", "client.buildings", {"buildingID", "name", "timezone"}, {"client.buildings.buildingID", "client.buildings.name", "client.buildings.timezone"}),
#"Invoked Custom Function" = Table.AddColumn(#"Expanded client.buildings1", "Get circuits", each #"Get circuits"([client.buildings.buildingID])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Get circuits"}),
#"Expanded Get circuits" = Table.ExpandTableColumn(#"Removed Errors", "Get circuits", {"circuit.circuitID", "circuit.deviceName", "circuit.channel", "circuit.load_id"}, {"Get circuits.circuit.circuitID", "Get circuits.circuit.deviceName", "Get circuits.circuit.channel", "Get circuits.circuit.load_id"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Get circuits", {"client.buildings.timezone"}, tblTimeZones, {"Time Zone"}, "tblTimeZones", JoinKind.LeftOuter),
#"Expanded tblTimeZones" = Table.ExpandTableColumn(#"Merged Queries1", "tblTimeZones", {"Hours Difference"}, {"tblTimeZones.Hours Difference"}),
#"Added Custom" = Table.AddColumn(#"Expanded tblTimeZones", "StartTS", each ((enteredStartDate - [tblTimeZones.Hours Difference]/24) - Number.From(Date.From(#datetime(1970,1,1,0,0,0)))) * 86400),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "EndTS", each ((enteredEndDate + 1 - [tblTimeZones.Hours Difference]/24) - Number.From(Date.From(#datetime(1970,1,1,0,0,0)))) * 86400),
#"Invoked Custom Function1" = Table.AddColumn(#"Added Custom1", "Get energy data", each #"Get energy data"([Get circuits.circuit.circuitID], [StartTS], [EndTS], [tblTimeZones.Hours Difference])),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Invoked Custom Function1", {"Get energy data"}),
#"Expanded Get energy data" = Table.ExpandTableColumn(#"Removed Errors1", "Get energy data", {"LocalTime", "Total kWh", "Operating Hours", "Circuit Average kW"}, {"energy data.LocalTime", "energy data.Total kWh", "energy data.Operating Hours", "energy data.Circuit Average kW"}),
#"Grouped Rows" = Table.Group(#"Expanded Get energy data", {"client.name", "client.buildings.name", "Get circuits.circuit.load_id", "energy data.LocalTime"}, {{"Load kWh", each List.Sum([energy data.Total kWh]), type number}, {"Load Operating Hours", each List.Average([energy data.Operating Hours]), type number}, {"Load Average kW", each List.Sum([energy data.Circuit Average kW]), type number}}),
#"Invoked Custom Function2" = Table.AddColumn(#"Grouped Rows", "Work Order Details", each #"Get work order details"([Get circuits.circuit.load_id], [energy data.LocalTime])),
#"Filtered Rows" = Table.SelectRows(#"Invoked Custom Function2", each ([energy data.LocalTime] <> null)),
#"Expanded Work Order Details" = Table.ExpandTableColumn(#"Filtered Rows", "Work Order Details", {"kW Pre", "kW Post", "Daily Run Hours"}, {"Work Order Details.kW Pre", "Work Order Details.kW Post", "Work Order Details.Daily Run Hours"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Work Order Details",null,0,Replacer.ReplaceValue,{"Work Order Details.kW Pre"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value", "Baseline kWh", each [Load Operating Hours] * [Work Order Details.kW Pre]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "kWh Saving", each if [Baseline kWh] <= 0 then 0 else [Baseline kWh]-[Load kWh])
in
#"Added Custom3"My updated custom function:
(buildingID as number)=>
let
Source = Json.Document(Web.Contents("https://apiv2.***********.com", [RelativePath = "v2.0/circuits", Query=[building_id=Text.From(buildingID)],Headers=[Authorization="*********************************"]])),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"circuitID", "deviceID", "device_id", "deviceName", "channel", "load_id"}, {"circuit.circuitID", "circuit.deviceID", "circuit.device_id", "circuit.deviceName", "circuit.channel", "circuit.load_id"})
in
#"Expanded Column1"If I invoke the custom funtion on its own, works fine so confident I've structured that correctly.
If I step through the query, the data is being retreived correctly, however when I get to the "#Invoked Custom Function" line I receive the same error message per below:
The base URL in both the query and custom function are identical.
Any other thoughts?