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 : )

[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 : )