Forum Discussion
Power BI Dataflow: Formula Firewall error with PostgreSQL, JSON transforms, and Custom Functions
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 : )
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
TranslatedTablelet's see if is enough to solve the problem : )
Community SupportHi 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.