Formula.Firewall error with invoked function on named range of Excel Sheet

[Watever]

Hi everyone,

The problem is not in Power-Bi but in Excel using Power Query, I hope someone can still help me.

I have firewall problems on some of my files because user press "cancel" instead of "ignore". I was hopping to remove those privacy problems that require to select "ignore".

I realized that it comes from using a custom invoke function to get value of a named range.

This is my function :

let
    Source = (rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
in
    Source

If I use it like that in my step : 

#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Entrepot] = GetValue("ID_Warehouse")),

 It will trigger a Formula.Firewall error unless I select ignore all privacy errors.

But if I use this instead :

ID_WH = Excel.CurrentWorkbook(){[Name="ID_Warehouse"]}[Content]{0}[Column1],
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Entrepot] = ID_WH),

I don't have the error and no issue with the end users. 

If I change my function to this instead, I still have same issue.

(rangeName) =>
let 
    answer = Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
in
    answer

Why would that be ? Am I missing something ?

Thank you

[v-heq-msft]

Hi @Watever ,

Based on your description, the Formula.Firewall error you are experiencing may be due to the privacy level setting of Power Query, which protects the privacy of your data by preventing unauthorized combinations of data from different sources that could expose sensitive information. When you use a custom function, Power Query treats it as a separate data source. The privacy levels of these data sources may conflict, resulting in Formula.Firewall errors.
You can try modifying the merge privacy level rules in excel.Data -> Query Options ->Always ignore Privacy Level settings.
Or combine data sources in a single query instead of using a custom function, which avoids privacy level conflicts.

let
    GetValue = (rangeName as text) as any =>
    let
        Source = Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
    in
        Source,

    ID_WH = GetValue("ID_Warehouse"),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Entrepot] = ID_WH)
in
    #"Filtered Rows1"

Privacy levels in Power Query - Power Query | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Watever]

Thank you for your help.

It's strange that it's treated as another data source and that you can't change the privacy of a custom function data source.

I other files that I used a table instead of named range cell, and I don't have the privacy issue on theses.

(ParameterName as text) =>
let
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parametre] = ParameterName)),
    Value=
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Valeur")

in
Value

Not sure where the difference is between both.