Formula.Firewall Please rebuild this data combination only when using function

[athrane]

Hi Everyone,

I am running into the error: Formula.Firewall: Query 'Get EL Single' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination like many others on this forum. However, this error only exist when I pull data using a function, not if I manually recreate the steps in the function.

Basicly I need to merge 1 API source (called "CO2 API DK2" below) into many other indipendant queries. All these indidenpant queries are from the same API-source, but have a different parameter called dataviewID. Below I have posted a mcode for the steps in which I pull data from the API, transform it, merge it with "CO2 API DK2" and voila, all works great and I am not getting any error. 

let
    dataviewID = "26653",

    Unix_start_of_year = Number.ToText(Duration.TotalSeconds(#datetime(Date.Year(DateTime.LocalNow())-1, 1,1,0,0,0) - #datetime(1970,1,1,0,0,0) - #duration(0,1,0,0))),
    CO2_table = #"CO2 API DK2",
    Source = Json.Document(Web.Contents("https://core.eniscope.com/v1/readings/", 
        [Headers=[#"X-Eniscope-API"="XXXXXXX", 
        Authorization="Basic XXXXXXXX"],
        RelativePath = dataviewID & "/?action=summarise&res=3600&fields[]=E&daterange[]=" & Unix_start_of_year & "&daterange[]=today"])),
    #"Converted to Table1" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table1"),
    #"Removed Columns2" = Table.RemoveColumns(#"Transposed Table",{"Column1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column14"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns2",1),
    #"Expanded Column13" = Table.ExpandListColumn(#"Removed Top Rows", "Column13"),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Column13", "Column13", {"ts", "E"}, {"ts", "E"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"ts", Int64.Type}, {"E", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0, [ts])),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "Datetime DK", each #"Daylight Saving"([Custom])),
    #"Added Custom1" = Table.AddColumn(#"Invoked Custom Function", "MWh", each if [E]= "null" then 0 else try [E]/1000000 otherwise 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Datetime DK", type datetime}, {"MWh", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ts", "E", "Custom"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Datetime DK"}, CO2_table, {"Datetime DK"}, "CO2 API", JoinKind.LeftOuter),
    #"Expanded CO2 API" = Table.ExpandTableColumn(#"Merged Queries", "CO2 API", {"CO2 average"}, {"CO2 average"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded CO2 API", "CO2 Emission", each try [CO2 average]*[MWh] otherwise 0),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"CO2 Emission", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Datetime DK", Order.Descending}}),
    #"Inserted Date" = Table.AddColumn(#"Sorted Rows", "Date", each DateTime.Date([Datetime DK]), type date),
    #"Changed Type3" = Table.TransformColumnTypes(#"Inserted Date",{{"Date", type datetime}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type3", "Location", each #"Location Table"[Location]{2}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom4",{{"Location", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type5",{"CO2 average"})
in
    #"Removed Columns1"

Output:

As I need to make this query for many many different "dataviewID"s, I created a function in which I am simply able to input the ID and it creates a new query. The only difference between this function and the mcode above is that "dataviewID" is moved up as an input:

(dataviewID as text) =>
let

    Unix_start_of_year = Number.ToText(Duration.TotalSeconds(#datetime(Date.Year(DateTime.LocalNow())-1, 1,1,0,0,0) - #datetime(1970,1,1,0,0,0) - #duration(0,1,0,0))),
    CO2_table = #"CO2 API DK2",
    Source = Json.Document(Web.Contents("https://core.eniscope.com/v1/readings/", 
...
...
...
in
    #"Removed Columns1"

If I now call this function with ID 26653 as I used in the original query, I get the following error:

How come this error only occures when using it as a function, and not if I use the exact same mcode but hardcode the "datavewID"?

Thanks so much!

[lbendlin]

Read about partitions in Power Query

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn