Formula.Firewall error when invoking custom funtion that references another data source

[cameroon]

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 individual circuit for each building via another api.  The last part where it was retreiving the individual circuits was being done by invoking a custom function.

Previously everything was working fine, however it is now being caught in the Formula.Firewall due to the buidling api call and the circuit api call coming from different URLs.

Query

let
    Source = Json.Document(Web.Contents("https://apiv2.***********.com/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]))
in
    #"Invoked Custom Function"

My custom funtion is 

(buildingID as number)=>
let
    Source = Json.Document(Web.Contents("https://apiv2.***********.com/v2.0/circuits?building_id="&Text.From(buildingID), [Headers=[*********************************************]])),
    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"

The buildingID used in the custom funtion is fed from the list of building retrieved and filtered in the query.

My understanding is that I need to try and incorporate the funtion into the query.  I tried to add a custom column where the column value was the custom function (a nested let statement) however this just returned a long list of null records.

M language is not my strength, so I am sure this is a relatively simple fix - I just don't know what it is.

Thanks in advance

[lbendlin]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[cameroon]

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?