Query Firewall Violation

[rpiboy_1]

I've read, watched videos and had lots of conversation and analysis with Perplexity. I have a query that by neccessity needs to get data from one REST endpoint then use that data to query a second, distinct REST endpoint. There are a number of static values defined in tables held in M or as parameters or simple text queries to allow the two queries to dynamically iterate. Originally it was  broken out as a series of seperate queries and functions neither approach has resolved the issue in the Service, which is getting the denial of "...rebuild this combination...".

I've posted the immediate query generating the issue, the specific step cited by the error is "ObjectDataWithFunction".

Am I missing something? Is there some other strategy I can or should employ, should I just go buy 'the book' on M?

let
    // CAPTURE ALL STATIC REFERENCES
    ServerCodesData = lst_ServerCodes,
    ProjectDataTypesData = lst_ProjectDataTypes,
    ServerNamesData = lst_ServerNames,    
    ObjectTypesData = ObjectTypes,
    ObjectColumnsData = #"Object Columns",
    DataTypesData = DataTypes,
    PaginationValue = #"Pagination Value",
    PaginationSkipValue = PaginationSkipVal,

    // PREP DYNAMIC DATA FOR ADMIN QUERY
    AdminCrossJoin = Table.AddColumn(ServerCodesData, "ProjectAdminDataType", each ProjectDataTypesData),
    AdminRenamedColumns = Table.RenameColumns(AdminCrossJoin,{{"Id", "Server.Id"}}),
    AdminExpanded = Table.ExpandTableColumn(AdminRenamedColumns, "ProjectAdminDataType", {"ProjDataTypes"}, {"ProjDataTypes"}),
    // CALL ADMIN ENDPOINTS
    AdminWithData =
        Table.AddColumn(
            AdminExpanded,
            "AdminData",
            each 
                let
                    AdminDataType = [ProjDataTypes],
                    ServerCode = [ServerCodes],
                    IsProject =
                        if AdminDataType = "projects" then
                            if ServerCode = "US" then
                                Json.Document(Web.Contents("https://adm-us.drofus.com/", [
                                    Headers=[Accept="application/json"],
                                    RelativePath = AdminDataType,
                                    Query = [show_all="1"]
                                ]))
                            else if ServerCode = "CA" then
                                Json.Document(Web.Contents("https://adm-ca.drofus.com/", [
                                    Headers=[Accept="application/json"],
                                    RelativePath = AdminDataType,
                                    Query = [show_all="1"]
                                ]))
                            else "No Server"
                        else
                            if ServerCode = "US" then
                                Json.Document(Web.Contents("https://adm-us.drofus.com/", [
                                    Headers=[Accept="application/json"],
                                    RelativePath = AdminDataType
                                ]))
                            else if ServerCode = "CA" then
                                Json.Document(Web.Contents("https://adm-ca.drofus.com/", [
                                    Headers=[Accept="application/json"],
                                    RelativePath = AdminDataType
                                ]))
                            else "No Server"
                in
                    IsProject
        ),    
    AdminCleaned = Table.RemoveRowsWithErrors(AdminWithData, {"AdminData"}),
    
    // PROCESS DATABASES AND PROJECTS USING FUNCTIONS
    AdminData = AdminCleaned,  
    DatabasesTable = 
        // FnProcessAdminDatabases
        let        
            DatabaseRows = Table.SelectRows(AdminData, each ([ProjDataTypes] = "database")),
            DatabaseExpanded = Table.ExpandListColumn(DatabaseRows, "AdminData"),
            DatabaseExpanded1 = Table.ExpandRecordColumn(DatabaseExpanded, "AdminData", {"database"}, {"database"}),
            DatabaseExpanded2 = Table.ExpandRecordColumn(DatabaseExpanded1, "database", {"name", "description", "backup_started", "updated_by", "created_by"}, {"name", "description", "backup_started", "updated_by", "created_by"}),
            DatabaseTyped = Table.TransformColumnTypes(DatabaseExpanded2, {{"name", type text}, {"description", type text}, {"updated_by", type text}, {"created_by", type text}}),
            DatabaseWithIndex = Table.AddIndexColumn(DatabaseTyped, "Database.Id", 1, 1, Int64.Type),
            DatabaseWithCode = Table.AddColumn(DatabaseWithIndex, "Server.DB.Code", each Text.Combine({Text.From([Server.Id]), [name]}, "."), type text),
            DatabaseWithServerNames = Table.NestedJoin(DatabaseWithCode, {"Server.Id"}, ServerNamesData, {"Id"}, "ServerNames", JoinKind.LeftOuter),
            DatabaseTable = Table.ExpandTableColumn(DatabaseWithServerNames, "ServerNames", {"Server Name"}, {"Server Name"})
        in
            DatabaseTable,

    ProjectsTable =
        // FnProcessAdminProjects  
        let
            ProjectRows = Table.SelectRows(AdminData, each ([ProjDataTypes] = "projects")),
            ProjectExpanded = Table.ExpandListColumn(ProjectRows, "AdminData"),
            ProjectExpanded1 = Table.ExpandRecordColumn(ProjectExpanded, "AdminData", {"project"}, {"project"}),
            ProjectExpanded2 = Table.ExpandRecordColumn(ProjectExpanded1, "project", {"id", "database_id", "no", "name", "owner_id", "description", "contact", "status", "updated", "gross_area", "active", "created_at", "created_by", "updated_by", "contract_ref", "global_id", "project_type_id", "country_code", "copied_from", "expires_on", "owner", "project_type"}, {"id", "database_id", "no", "name", "owner_id", "description", "contact", "status", "updated", "gross_area", "active", "created_at", "created_by", "updated_by", "contract_ref", "global_id", "project_type_id", "country_code", "copied_from", "expires_on", "owner", "project_type"}),
            ProjectTyped = Table.TransformColumnTypes(ProjectExpanded2, {{"database_id", type text}, {"name", type text}, {"description", type text}, {"contact", type text}, {"created_by", type text}, {"updated_by", type text}, {"global_id", type text}, {"copied_from", type text}, {"country_code", type text}, {"expires_on", type text}, {"created_at", type datetimezone}, {"project_type_id", Int64.Type}, {"contract_ref", type text}, {"active", type logical}, {"gross_area", type number}, {"owner_id", Int64.Type}, {"no", type text}, {"Server.Id", type text}, {"id", Int64.Type}}),
            ProjectWithDbCode = Table.AddColumn(ProjectTyped, "Server.DB.Code", each Text.Combine({Text.From([Server.Id]), [database_id]}, "."), type text),
            ProjectWithDatabase = Table.NestedJoin(ProjectWithDbCode, {"Server.DB.Code"}, DatabasesTable, {"Server.DB.Code"}, "DatabaseInfo", JoinKind.LeftOuter),
            ProjectWithDbId = Table.ExpandTableColumn(ProjectWithDatabase, "DatabaseInfo", {"Database.Id"}, {"Database.Id"}),
            ProjectCleaned = Table.RemoveColumns(ProjectWithDbId, {"Server.DB.Code", "Server.Id"}),
            ProjectWithId = Table.AddColumn(ProjectCleaned, "DB.Proj.Id", each Text.Combine({Text.From([Database.Id], "en-US"), Text.From([id], "en-US")}, "."), type text),
            ProjectWithUniqueId = Table.AddIndexColumn(ProjectWithId, "Proj.Unique.Id", 1, 1, Int64.Type),
            ProjectWithDates = Table.AddColumn(ProjectWithUniqueId, "Created Date (UTC)", each DateTimeZone.SwitchZone([created_at], 0), DateTimeZone.Type),
            ProjectWithTime = Table.DuplicateColumn(ProjectWithDates, "Created Date (UTC)", "Created Time (UTC)"),
            ProjectTimeExtracted = Table.TransformColumns(ProjectWithTime, {{"Created Time (UTC)", DateTime.Time, type time}}),
            ProjectDateExtracted = Table.TransformColumns(ProjectTimeExtracted, {{"Created Date (UTC)", DateTime.Date, type date}}),
            ProjectLocalDate = Table.AddColumn(ProjectDateExtracted, "Created Date (local)", each DateTime.Date([created_at]), type date),
            ProjectLocalTime = Table.AddColumn(ProjectLocalDate, "Created Time (local)", each DateTime.Time([created_at]), type time),
            ProjectWithTimezone = Table.TransformColumns(ProjectLocalTime, {{"created_at", each DateTimeZone.ZoneHours(_) + DateTimeZone.ZoneMinutes(_)/60.0, type number}}),
            ProjectTable = Table.RenameColumns(ProjectWithTimezone, {{"created_at", "Timezone Offset"}})
        in
            ProjectTable,    
    
    UsersTable =
        // FnProcessAdminUsers  
        let
            //seperate table to be outputted later, not used in problematic step
        in
            #"Changed Type",
    
    //DATABASE FILTERS
    DatabaseFiltersExcludeData = 
        if #"Exclude Database Filter" <> null
        then
            let
                //code to build filter string
            in
                #"Expanded dRofus admin database"
        else
            null,

    DatabaseFiltersIncludeData =        
        if #"Inclusion Database Filter" <> null
        then
            let
                //code to build filter string
            in
                #"Expanded dRofus admin database"
        else
            null,    
    
    // CONTINUE WITH OBJECT TYPE PROCESSING
    ProjectsForObjectTypes = Table.SelectColumns(ProjectsTable, {"id", "Proj.Unique.Id", "Database.Id", "database_id", "no"}),
    ProjectsWithServerCodes = Table.NestedJoin(ProjectsForObjectTypes, {"Database.Id"}, DatabasesTable, {"Database.Id"}, "DatabaseInfo", JoinKind.LeftOuter),
    ProjectsWithCodes = Table.ExpandTableColumn(ProjectsWithServerCodes, "DatabaseInfo", {"ServerCodes"}, {"ServerCodes"}),
    
    // APPLY FILTERS
    ExcludedFiltered = if DatabaseFiltersExcludeData <> null then
        let FilterMerge = Table.NestedJoin(ProjectsWithCodes, {"Database.Id"}, DatabaseFiltersExcludeData, {"Id"}, "ExcludeFilter", JoinKind.LeftAnti)
        in Table.RemoveColumns(FilterMerge, {"ExcludeFilter"})
    else ProjectsWithCodes,
    
    IncludedFiltered = if DatabaseFiltersIncludeData <> null then
        let FilterMerge = Table.NestedJoin(ExcludedFiltered, {"Database.Id"}, DatabaseFiltersIncludeData, {"Id"}, "IncludeFilter", JoinKind.Inner)
        in Table.RemoveColumns(FilterMerge, {"IncludeFilter"})
    else ExcludedFiltered,

    // PROCESS OBJECT TYPES
    ObjectTypesList = ObjectTypesData[ObjectTypes],
    ProjectsWithObjectTypes = Table.AddColumn(IncludedFiltered, "ObjectTypes", each ObjectTypesList),
    ProjectsObjectExpanded = Table.ExpandListColumn(ProjectsWithObjectTypes, "ObjectTypes"),
    
    DBTypes = Table.Column(Table.SelectRows(ObjectTypesData, each [Filter] = "database"), "ObjectTypes"),
    PRTypes = Table.Column(Table.SelectRows(ObjectTypesData, each [Filter] = "project"), "ObjectTypes"),
    DatabaseTypeRows = Table.SelectRows(ProjectsObjectExpanded, each List.Contains(DBTypes, [ObjectTypes])),
    DatabaseTypesDeduped = Table.Distinct(DatabaseTypeRows, {"Database.Id", "ObjectTypes"}),
    ProjectTypeRows = Table.SelectRows(ProjectsObjectExpanded, each List.Contains(PRTypes, [ObjectTypes])),
    CombinedForProcessing = Table.Combine({DatabaseTypesDeduped, ProjectTypeRows}),
    
    // CALL OBJECT TYPE FUNCTION
    ObjectDataWithFunction =
        Table.AddColumn(
            CombinedForProcessing,
            "Data",            
            each    
                let
                    serverCode = [ServerCodes],
                    databaseName = [database_id],
                    projectNo = [no],
                    objectType = [ObjectTypes],
                    Proj.Unique.Id = [Proj.Unique.Id],
                    Database.Id = [Database.Id],        
                    FieldNameColumnName = "Name",
                                        
                    ObjectColumnsMeta = Table.SelectRows(ObjectColumnsData, each [ObjectType] = objectType),
                    //lists
                    ColumnIDs = Table.Column(ObjectColumnsMeta, "dRofus.ID"),
                    ColumnNames = Table.Column(ObjectColumnsMeta, FieldNameColumnName),
                    ColumnDataTypesRaw = Table.Column(ObjectColumnsMeta, "PQ.DataType"),
                    //datatypes
                    MapDataTypes = List.Transform(ColumnDataTypesRaw, each if Record.HasFields(DataTypesData, _) then Record.Field(DataTypesData, _) else type any),
                    //select string
                    ColumnStrings = Text.Combine(ColumnIDs, ","),

                    //REST API GET function
                    GetWebContents = (topVal as number, skipVal as number) =>
                        let
                            GenerateRelativePath = "?$top=" & Number.ToText(topVal) & "&$skip=" & Number.ToText(skipVal) & "&$select=" & ColumnStrings,
                            
                            GetData = if serverCode = "US" then
                                Json.Document(Web.Contents("https://api-us.drofus.com/api/", [RelativePath= databaseName & "/" & projectNo & "/" & objectType & GenerateRelativePath]))
                            else if serverCode = "CA" then
                                Json.Document(Web.Contents("https://api-ca.drofus.com/api/", [RelativePath= databaseName & "/" & projectNo & "/" & objectType & GenerateRelativePath]))
                            else
                                error "Unsupported server code: " & serverCode
                        in
                            GetData,

                    //pagination iterator
                    RawDataTryResult = try
                        let
                            Source = List.Generate(
                                ()=> [
                                    Result = try GetWebContents(PaginationValue, PaginationSkipVal) otherwise null,
                                    tVal = PaginationValue,
                                    sVal = PaginationSkipVal
                                ],
                                each [Result] <> null and not List.IsEmpty([Result]),
                                each [
                                    Result = try GetWebContents(PaginationValue, [sVal] + [tVal]) otherwise null,
                                    tVal = PaginationValue,
                                    sVal = [sVal] + [tVal]
                                ],
                                each [Result]
                            ),
                            FlattenedResults = List.Combine(Source),
                        
                            CheckForEmptyResults =
                                if List.IsEmpty(FlattenedResults) then
                                    error Error.Record(
                                        "Not Data Found",
                                        "The " & objectType & " query returned no results for: " & serverCode & "\" & databaseName & "\" & projectNo,
                                        [
                                            ServerCode = serverCode,
                                            DatabaseName = databaseName,
                                            ProjectNo = projectNo,
                                            ObjectType = objectType
                                        ]
                                    )
                                else
                                    FlattenedResults        
                        in
                            CheckForEmptyResults,

                    //process data            
                    FinalResult =
                        if RawDataTryResult[HasError] then
                            error Error.Record(
                                "Data Retrieval Error",
                                "Failed to get data from API",
                                [                    
                                    ObjectType = objectType,
                                    ServerCode = serverCode,
                                    DatabaseName = databaseName,
                                    ProjectNo = projectNo,                    
                                    ErrorDetail = Text.From(RawDataTryResult[Error][Detail]? ?? "")                    
                                ]
                            )
                        else
                            try
                                let
                                    RawData = RawDataTryResult[Value],                    
                                    ProjectDataTable = Table.FromColumns({{Database.Id}, {Proj.Unique.Id}, {RawData}}, {"Database.Id", "Proj.Unique.Id", "Data"}),                    
                                    #"Expanded Data" = Table.ExpandListColumn(ProjectDataTable, "Data"),
                                    #"Expanded Columns" = Table.ExpandRecordColumn(#"Expanded Data", "Data", ColumnIDs, ColumnNames),
                                    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Columns", List.Zip({ColumnNames, MapDataTypes}))                            
                                in
                                    #"Changed Type"                    

                            otherwise
                                error Error.Record(
                                    "Transformation Error",
                                    "Error occurred during data transformation",
                                    [                        
                                        ObjectType = objectType,
                                        ServerCode = serverCode,
                                        DatabaseName = databaseName,
                                        ProjectNo = projectNo,                        
                                        ErrorDetail = "Check column mappings and data types",                        
                                        ErrorSource = "Transformation" 
                                    ]
                                )
                                                                
                in  
                    FinalResult    
        ),

[v-kpoloju-msft]

Hi @rpiboy_1, 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @VahidDM, for his inputs on this thread. 

Thanks a lot for sharing all the details and the code. I can see the amount of effort you have already put into this. The error you are seeing (Query Firewall Violation with “...rebuild this combination...”) isn’t about your M code being wrong, but rather how Power Query’s privacy firewall works.

What’s happening is:
• Your query pulls data from two different REST endpoints (adm-us/ca and api-us/ca).
• Power Query treats each endpoint as a separate data source.
• When you try to use data from one endpoint as input into the other, the firewall blocks it for privacy reasons.

This is why the step ObjectDataWithFunction is called out that’s where the combination is happening. Here couple of approaches mentioned below please try those:

Align Privacy Levels: In Power BI Desktop go to File > Options & Settings > Data Source Settings and make sure both endpoints are set to the same privacy level (for example, Organizational). You can also adjust this under File > Options > Privacy by choosing to ignore privacy levels for this file (use with caution, only if you fully trust both sources).
https://learn.microsoft.com/en-us/power-query/privacy-levels 

If one query feeds into another, sometimes using Table.Buffer() on the first query breaks the “live link” that triggers the firewall. https://learn.microsoft.com/en-in/powerquery-m/table-buffer

Load the first API into a Power BI Dataflow, then call the second API in your report. Because the first dataset materialized in the service, the firewall doesn’t block the combination. https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-introduction-self-ser... 

If the endpoint lets you pull related data in one request (e.g., via an expand or include query parameter), that avoids the need for chaining two sources. The issue is not with your logic, but with how Power Query enforces privacy when combining multiple data sources. Adjusting the privacy settings or restructuring the queries should let you get past this.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[rpiboy_1]

I would love to use Dataflows, but in this case I'm building a PBI template for distribution to our customers to use to get their data from our SAAS solution. It seems like as much as I wanted to retain if/then logic around the different regional endpoints to my dismay I'm going to be forced to generate a unique query for each unique endpoint.

[v-kpoloju-msft · Accepted solution]

Hi @rpiboy_1, 
Thank you for the follow-up question. To clarify having a web call inside a function doesn’t by itself violate the firewall rules.

The firewall is triggered when:
• A value from one data source (e.g. admin API) is used to dynamically build or drive a request to another data source (e.g. object API).
• Power Query can’t guarantee that sensitive information from one source isn’t being sent to the other.

So technically, whether the Web.Contents is inside a function, or a “stand-alone” query doesn’t matter what matters is how the privacy levels are configured for the sources you’re combining. https://learn.microsoft.com/en-us/powerquery-m/web-contents 

Function per server: You can safely parameterize the base URL (US, CA, etc.) in your function. If each function only calls one domain, the firewall won’t block it.

Separate queries per server, then roll up: This also works, but it’s more verbose. You’d then combine those queries later.

Both approaches are valid. The key is: if the endpoints are trusted and belong to the same system, set their Privacy Level to the same scope (e.g. Organizational). That way, the firewall won’t block cross-source logic whether it’s inside a function or separate queries.

So the choice of “function vs. separate queries” is mostly about maintainability of your code rather than avoiding the firewall. The firewall decision is based on privacy settings, not function structure.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @rpiboy_1,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @rpiboy_1,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @rpiboy_1,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[VahidDM]

Short answer: you’re hitting the privacy/combination firewall because that step calls different hosts per row (https://adm-us…, https://adm-ca…, https://api-us…, https://api-ca…). In the Service, a single query that mixes multiple data sources (different domains) will be blocked unless you stage them or give them the same privacy level. Fix by staging each host separately, then append.

What to change (minimal pattern)

Create one staged query per host (load = Off).
Use RelativePath/Query only; no concatenated URLs.

// US admin
Admin_US = (relative as text, optional qry as nullable record) as any =>
Json.Document(
Web.Contents(
"https://adm-us.drofus.com",
[ RelativePath = relative, Query = if qry=null then [] else qry, Headers=[Accept="application/json"] ]
)
);

// CA admin
Admin_CA = (relative as text, optional qry as nullable record) as any =>
Json.Document(
Web.Contents(
"https://adm-ca.drofus.com",
[ RelativePath = relative, Query = if qry=null then [] else qry, Headers=[Accept="application/json"] ]
)
);

// US api
Api_US = (relative as text, optional qry as nullable record) as any =>
Json.Document(
Web.Contents(
"https://api-us.drofus.com",
[ RelativePath = relative, Query = if qry=null then [] else qry ]
)
);

// CA api
Api_CA = (relative as text, optional qry as nullable record) as any =>
Json.Document(
Web.Contents(
"https://api-ca.drofus.com",
[ RelativePath = relative, Query = if qry=null then [] else qry ]
)
);

Branch your processing by host, call the proper function, then append:

USRows = Table.SelectRows(CombinedForProcessing, each [ServerCodes] = "US");
CARows = Table.SelectRows(CombinedForProcessing, each [ServerCodes] = "CA");

US_Data =
Table.AddColumn(
USRows, "Data",
each Api_US(
[database_id] & "/" & [no] & "/" & [ObjectTypes],
[#"${top}"= PaginationValue, #"${skip}"= PaginationSkipVal, $"select"= Text.Combine(ColumnIDs, ",")]
)
);

CA_Data =
Table.AddColumn(
CARows, "Data",
each Api_CA(
[database_id] & "/" & [no] & "/" & [ObjectTypes],
[#"${top}"= PaginationValue, #"${skip}"= PaginationSkipVal, $"select"= Text.Combine(ColumnIDs, ",")]
)
);

ObjectDataWithFunction = Table.Combine({US_Data, CA_Data});

In the Service, set Privacy Level = Organizational (or the same level) for each of these four roots:
https://adm-us.drofus.com, https://adm-ca.drofus.com, https://api-us.drofus.com, https://api-ca.drofus.com.
Do not use dynamic hosts in a single Web.Contents call.

Alternatives

Put each host in a dataflow and have the dataset read the dataflows (single source).

Use a small proxy/Azure Function so the dataset calls one domain.

As a last resort, enable “Allow combining data from multiple sources” (Privacy: Ignore) — acceptable only if your org permits it.

Why the error: your ObjectDataWithFunction step evaluates different domains row-by-row; the firewall can’t verify safe combination, so it blocks it. Staging per host (or same privacy level) resolves it.

[rpiboy_1]

Will having a web call in a function itself violate the firewall rules? I.E. can I have a function per server, or do I need to have a seperate, stand-alone query for each Server that I then roll-up the data from? Its a bit pandentic as I still have to have 'something' for each server, just impacts how I approach the full solution.

[rpiboy_1]

Ugh, thanks, so basically I need a query per host/static URL. That is unfortunate makes management a bit more of a pain and also makes it 'harder' to do some other things I had hoped to do in the future. I was aware of the need to stay away from dynamic (concatended) URLs and make sure to use the arguements of the Web.Contents function to build dynamic queries. Privacy levels are not a concern in this case and everything has been set to Organizational.