Formula Firewall Error - Business Central API Issue

[KedielSanchez]

Hello community. Forgive me if my english is not good, english it's not my first language.

My current employer implemented Business Central (Web) for the ERP to run the company and I have my fair amounts of gaps on the API and structure side of BC, but there are 3 main APIs available when I connect Power BI to BC: WebServices (Legacy), Advanced APIs and Standard APIs v2.0.

And we have multiple companies so the reports should work as a consolidation solution.

 

The issue starts when I try to use a custom function to join multiple tables from any of the APIs. This is a query that result in the Firewall error:

let
    Receta_fx = (Empresas as text) =>
    let
        Origen = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
        IGC_CONF = Origen{[Name="PRODUCTION"]}[Data],
        TESTPBI = IGC_CONF{[Name=Empresas]}[Data],
        WebServices = TESTPBI{[Name="WebServices"]}[Data],
        Receta_table = WebServices{[Name="Receta",Signature="table"]}[Data],
        RecetaProdBOMLine = Table.ExpandTableColumn(Receta_table, "RecetaProdBOMLine", {"Production_BOM_No", "Version_Code", "Line_No", "Type", "No", "Variant_Code", "Description", "Calculation_Formula", "Length", "Width", "Depth", "Weight", "Quantity_per", "Unit_of_Measure_Code", "Scrap_Percent", "Routing_Link_Code", "Position", "Position_2", "Position_3", "Lead_Time_Offset", "Starting_Date", "Ending_Date", "Receta", "Production_BOM_No_Link"}, {"Production_BOM_No", "Version_Code", "Line_No", "Type", "No.1", "Variant_Code", "Description.1", "Calculation_Formula", "Length", "Width", "Depth", "Weight", "Quantity_per", "Unit_of_Measure_Code.1", "Scrap_Percent", "Routing_Link_Code", "Position", "Position_2", "Position_3", "Lead_Time_Offset", "Starting_Date", "Ending_Date", "Receta", "Production_BOM_No_Link"})
    in
        RecetaProdBOMLine,
    Source = Table.SelectColumns( Companies, {"Company"}),
    CustFunct_Receta_fx = Table.AddColumn(Source, "Receta_Table_fx", each Receta_fx([Company]))
in
    CustFunct_Receta_fx

 

And If I place the function as a separate query I still get the Formula Firewall error even when the privacy level is set Organizational in all queries.

let
    Source = Companies,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Company"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "Receta", each Receta_fx_([Company]))
in
    #"Invoked Custom Function"

The same for trying to join two queries from different APIs. Another strange thing is that the report can get refreshed on the power bi service.

If there is no solution then I'll need to build a table for each company for any of the tables needed. This will make it more difficult to upkeep on desktop if this is not resolved.

[v-priyankata]

Hi @KedielSanchez,

We are grateful for your participation in the Microsoft Fabric Community forum.

Could you share a few additional details to help narrow down the issue? so we can try to provide the solution.
1. Which version of Power BI Desktop are you using?
2. Are you connecting to Business Central Online or an on-premises instance?
3. Does the error occur with all APIs (WebServices, Advanced, Standard v2.0) or just specific ones?

Thanks.

[KedielSanchez]

Hi @v-priyankata ! Thanks for your message.

1. March 2025

2. Online

3. All APIs. It replicates the issue if I use a custom fuction

[v-priyankata · Accepted solution]

Hello @KedielSanchez 
Thank you for reaching out to the Microsoft Fabric Forum Community. The Formula.Firewall error typically occurs when Power BI enforces data privacy levels between queries or data sources, especially when combining dynamic data access through custom functions or query parameters. Please consider the below steps:
 

• Ensure all data sources have the same privacy level to do this Go to File > Options and settings > Data source settings and Select each Business Central API source and click Edit Permissions Set all of them to Organizational (preferred if they are all internal)
• If you’re referencing one query inside another this can trigger the firewall. Instead load data from each source into separate staging queries and Merge or append the data afterward (all within the same privacy level).
• As a temporary workaround (not recommended for production) Go to File > Options and settings > Options > Current File > Privacy Set it to
  “Always Ignore the Privacy Level settings”.
• Consider using a Custom Connector for more advanced API usage (like retrieving data across multiple companies via functions), a custom connector can handle this logic in a safer and more scalable way.
  
   

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-priyankata]

Hi @KedielSanchez 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-priyankata]

Hi @KedielSanchez 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @KedielSanchez 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.