Power query dynamic filter error

[mlozano]

I am trying to create a dynamic filter with power query parameters, my data source is an Azure SQL database, the intention is to be able to filter the data by region dynamically from the report view.

I have the following code:

let
SelectedRegion = if Type.Is(Value.Type(RegionSelected), List.Type) then
Text.Combine({"'", Text.Combine(RegionSelected, "','"), "'"})
else
Text.Combine({"'", RegionSelected, "'"}),
selectAll = if Type.Is(Value.Type(RegionSelected), List.Type) then
List.Contains(RegionSelected, "_SelectAll_")
else
false,
query = "
SELECT * FROM abi_financial_maz_rpt.profit_loss_last_estimated_pe_rpt",
dynamicfilter = if selectAll then " "
else Text.Combine({" WHERE region_name in(", SelectedRegion, ")"}),
finalQuery = Text.Combine({query,dynamicfilter}),
Source = Sql.Database("edhazurencertdmgbqa.database.windows.net", "edhazurencertdmgbqa", [Query=finalQuery])
in
Source

Whenever I run my code from the power query advanced editor, my output is as follows

Formula.Firewall: The query 'Query 5' (step 'Source') references other queries or steps, so it cannot directly access the data source. Regenerate this data combination.*

*The security level is at none.

I also tried the following code but the error is the same

Sql.Database("edhazurencertdmgbqa.database.windows.net", "edhazurencertdmgbqa", [Query="SELECT * FROM abi_financial_maz_rpt.profit_loss_last_estimated_pe_rpt WHERE region_name = '"&RegionSelected&"'"])

The parameter is based on a list that was built with the region column:

               

Does anyone know how I can fix it or why it happens?

[v-kkf-msft · Accepted solution]

Hi @mlozano ,

This is because your Region query are sourced from other data sources or the same data source, causing the Power Query firewall error, please go to Options -> Privacy, and disable privacy.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.