Forum Discussion

Watever's avatar
Watever
Frequent Visitor
1 year ago

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

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Watever
Frequent Visitor
1 year ago

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.