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.

[KedielSanchez · Accepted solution]

I am so sorry for the delay in replying @v-priyankata.

Let's go through each suggestion:

1. All queries had the same privacy level set as Organizational, so not much to change there. I even tested as Public and None to see if that was the solution but no dice.

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)
2. I tried that at the beginning. Whether the function is used as a separate query or just as a step in the final query, the error still triggers — even with privacy levels set to Organizational.

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).
3. Tried it and failed.

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”.
4. Unfortunately, I don’t have the know-how to build one, and the company doesn’t have anyone with that capability either.

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.

Final approach

At the end of the day, I had to ditch the function that dynamically pulled the table from any company. Instead, I built a step-by-step standardized query that lets me target any company in the organization. I also created a non-standard method to extract the column names and types from the table schema.

I'll be using the 

VendorLedgerEntries 

table as an example of the WebServices API.

Helper Query:

This query connects to Business Central, navigates to the specific company and table, and extracts a list of the column names and their respective types in a format that can later be reused in other queries.

// ColType_and_ColName_of_AnyTable

let
// Connect to the Business Central environment
Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
// Select the PRODUCTION environment
PRODUCTION = Table.SelectRows(Source, each [Name]="PRODUCTION"),
// Get the list of companies in that environment
CompaniesList = PRODUCTION[Data]{0},
// Select a specific company
Companies = Table.SelectRows(CompaniesList, each [Name] = "NameOfCompany"),
// Load all APIs available for the selected company
APIsList = Companies[Data]{0},
// Filter the API group containing the table (WebServices in this case)
API = Table.SelectRows(APIsList, each [Name]="WebServices"),
// Load the list of available tables
TableList = API[Data]{0},
// Filter the desired table
TableSel = Table.SelectRows(TableList, each ([Name] = "VendorLedgerEntries")),
// Load the actual data of the table (not yet expanded)
Table = TableSel[Data]{0},
// Get the metadata (schema) of the table and filter out problematic types like Table.Type or Record.Type
TableMetadata = Table.SelectRows(
Table.Schema(Table),
each [Name] <> "ETag" and not (List.Contains({"Table.Type", "Record.Type"}, [TypeName]))
),
// Create a helper column with format { "ColumnName", ColumnType } as string
Helper_ColType = Table.AddColumn(
TableMetadata,
"Helper",
each Text.Combine({"{", Character.FromNumber(34)&[Name]&Character.FromNumber(34), ", ", [TypeName], "}"})
),
// Create another helper column just with the column names as strings
Helper_Col = Table.AddColumn(
Helper_ColType,
"Helper_ColName",
each Text.Combine({Character.FromNumber(34), [Name], Character.FromNumber(34)})
),
// Create a string that can be pasted in a Table.TransformColumnTypes step
String_ColType = "{" & Text.Combine(Helper_Col[Helper], ", ") & "}",
// Create a string with just the column names for use in ExpandTableColumn
Sting_ColName = "{" & Text.Combine(Helper_Col[Helper_ColName], ", ") & "}",
// Combine both strings with line breaks for easy pasting
String = Text.Combine({Sting_ColName, Character.FromNumber(10), Character.FromNumber(10), String_ColType})
in
    String

Main Query:
This query dynamically navigates the Business Central structure, selects one or multiple companies, finds the table, and expands the data using the column names and types extracted by the helper query above.

// VendorLedgerEntries


let
// Connect to Business Central root
Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
// Select the PRODUCTION environment
PRODUCTION = Table.SelectRows(Source, each [Name]="PRODUCTION"),
// Get the list of companies in the environment
CompaniesList = PRODUCTION[Data]{0},
// Filter desired company or companies (one or many depending on setup)
CompanieSel = Table.SelectRows(CompaniesList, each [Name] = "CompanyName"),
// To query multiple companies, just duplicate and OR multiple [Name] conditions
// Rename columns for clarity
Companies = Table.RenameColumns(
Table.SelectColumns(CompanieSel, {"Name", "Data"}),
{{"Name", "Compañía"}, {"Data", "APIs"}}
),
// Expand APIs available for each company
APIsList = Table.ExpandTableColumn(Companies, "APIs", Table.ColumnNames(Companie[APIs]{0})),
// Filter the API group to only WebServices
APISel = Table.SelectRows(APIsList, each [Name]="WebServices"),
// Rename columns again for clarity
API = Table.RenameColumns(
Table.SelectColumns(APISel, {"Compañía", "Data"}),
{"Data", "Tables"}
),
// Expand list of tables in WebServices
TableList = Table.ExpandTableColumn(API, "Tables", Table.ColumnNames(API[Tables]{0})),
// Select the target table: VendorLedgerEntries
TableSel = Table.SelectRows(TableList, each ([Name] = "VendorLedgerEntries")),
// Rename column to make it clear where the actual data is
Table = Table.RenameColumns(
Table.SelectColumns(TableSel, {"Compañía", "Data"}),
{"Data", "TableData"}
),
// Expand table data using column names from the helper query (paste upper part of helper)
Table_Data = Table.ExpandTableColumn(
Table,
"TableData",
/* Paste the list of column names from the helper query output */
),
// Apply correct column types using the second part of the helper query (paste bottom part of helper)
NativeColType = Table.TransformColumnTypes(
Table_Data,
/* Paste the list of { "ColumnName", Type } pairs from the helper query output */
)
in
NativeColType

[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]

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.