Power BI Dataflow: Formula Firewall error with PostgreSQL, JSON transforms, and Custom Functions

[Jacopo_Fabbri]

Hi everyone,

I'm hitting a Formula Firewall wall in a Power BI Service Dataflow and I need to resolve it without losing my complex transformation logic in the report_WA query.

The Setup:

• Source: PostgreSQL on Azure (ms5-db-postgre-prod-replica...).
• Query Complexity: The query report_WA is not a simple select. It includes:
1. JSON Transformations: Parsing nested JSON fields from the DB.
2. Function Calls: I am invoking two specific functions/steps within the M code: timezone and translatevalues.
• Total Queries: 46 queries in the same Dataflow, some of which are likely interacting or sharing these functions.

The Error: "Privacy settings don't allow the combination of data from multiple sources in the same query."

What I've tried:

• Before publishing -> Setting up the desktop file with the data origin privacy level to “organizational” or “public” and to set from the options to “ignore privacy levels”.
• Removing "red" steps (the ones that says are evalueted outside the data origin) temporarily, but the firewall error persists at the validation level with only the 2 steps “Source” and “Navigation”.
• Anyway I need a way to fix this without losing my transformation steps.

Thank you a lot : )

[Jacopo_Fabbri]

Good Afternoon,

Today i had time to work on it:

To do a test and understeand better the problem -> i created 2 different files with only 2 tabels “report_WA” and "report_s_Phase".

In both the files i keep the 2 functions but:

• In the first i recall the function in only one of the 2 tables -> and if i put this file on powerbi service -> there aren’t firewall problems in powerquery.
• In the first i recall the function in both the 2 tables -> and if i put this file on powerbi service -> there is the firewall block in powerquery

So i understood the problem are not the function themselves but how the firewall “panics” if they are recall by multiple tables.

So i created other 2 files with 2 different SOLUTIONS:

1)First file (Works but i discarded) -> your suggested solution of cramming all the 2 functions in each table and removing the separate object  functions -> it works but is not optimize and difficult to maintain/debug.

2)Second file (Works and i chose it because it’s faster and more dynamic(i tested both in service and desktop with the analyzer).

By creating "Load Disabled" staging queries, you split the execution graph into two safe phases. Phase 1: Connect and extract. Phase 2: Transform in memory. The firewall easily maps this dependency and allows the refresh to pass seamlessly in the Service.

Now i will procede with implementing this "stage solution" on my main file with all the tables.

I wish this solution can be useful for others in the future,

(if you see any problem in my logic let me know)

Thank you all : )

[lbendlin]

Mandatory viewing: https://www.youtube.com/watch?v=RbqCA6srkmM

[Jacopo_Fabbri]

Thank you, i will sure watch : )

[GilbertQ]

Hi @Jacopo_Fabbri 

The only way I have successfully got something similar to work like this is to have everything running within a single table. Typically the formula file rule comes up when you're having two tables with 2 different data sources and when you have a lot of complexity as you're explaining here. It then is very difficult to navigate multiple tables. While it can get pretty complex. That is what I have done in the past to overcome this formula firewall rule.

[Jacopo_Fabbri]

Thank you,

the data source is only 1 as a postgress DB, but i'm afraid is evaluating the functions as external tables.

So as you suggest i  will try to put the 2 following function inside each query that needs them (even if i don't like a so static structure)

(dateTimeUtc as nullable datetime) as nullable datetime =>
let
    result =
        if dateTimeUtc = null then
            null
        else
            let
                year = Date.Year(dateTimeUtc),

                // Ultima domenica di marzo (inizio ora legale)
                marchLastDay = #date(year, 3, 31),
                marchWeekday = Date.DayOfWeek(marchLastDay, Day.Sunday),
                dstStart = #datetime(year, 3, 31 - marchWeekday, 2, 0, 0),

                // Ultima domenica di ottobre (fine ora legale)
                octoberLastDay = #date(year, 10, 31),
                octoberWeekday = Date.DayOfWeek(octoberLastDay, Day.Sunday),
                dstEnd = #datetime(year, 10, 31 - octoberWeekday, 3, 0, 0),

                // Controlla se la data è in ora legale
                isDst = dateTimeUtc >= dstStart and dateTimeUtc < dstEnd,

                // Offset orario
                offsetHours = if isDst then 2 else 1,

                // Aggiungi offset
                converted = dateTimeUtc + #duration(0, offsetHours, 0, 0)
            in
                converted
in
    DateTime.From(result)

(tbl as table, translationTbl as table, columnsToTranslate as list) as table =>
let
    // 1. Assicuriamoci che la tabella delle traduzioni sia in RAM
    BufferedTranslations = Table.Buffer(translationTbl),
    
    // 2. Creiamo il dizionario (chiave -> valore)
    TranslationRecord = Record.FromList(
        BufferedTranslations[Value],
        BufferedTranslations[TranslationKey]
    ),

    // 3. Prepariamo le istruzioni: se c'è un null, lascia null. 
    // Altrimenti cerca nel dizionario. Se non trova la traduzione, tiene il valore originale.
    TransformOperations = List.Transform(
        columnsToTranslate,
        (colName) => {
            colName,
            each if _ = null then null else Record.FieldOrDefault(TranslationRecord, Text.From(_), _),
            type text
        }
    ),

    // 4. Applichiamo tutte le traduzioni alla tabella in una sola passata
    TranslatedTable = Table.TransformColumns(
        tbl, 
        TransformOperations, 
        null, 
        MissingField.Ignore
    )
in
    TranslatedTable

 let's see if is enough to solve the problem : )

[v-kpoloju-msft]

Hi @Jacopo_Fabbri, 

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

You are right in your observation, even though your source is a single PostgreSQL database, the Formula Firewall is likely triggered because your custom functions (timezone, translatevalues) and the translation table are being evaluated as separate queries. From Power BI’s perspective, this looks like combining multiple sources, which causes the error.

The most reliable fix is to ensure everything runs within a single query context. Instead of keeping those functions as separate queries, define them directly inside each query where they are used. Also, make sure the translation table is created or loaded within the same query, rather than referenced externally. This prevents Power BI from splitting the logic into multiple evaluation contexts.

Settings like “Ignore Privacy Levels” or using Table.Buffer() may help in some cases, but they are not consistently reliable in Dataflows. So, your approach of inlining the functions are the correct and most stable way to resolve this issue, as long as all external query references are removed.

Hope that clarifies. 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 @Jacopo_Fabbri, 

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.