Formula Firewall Error - Business Central API Issue

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