M Query Parameterized - Firewall error

[jdc_123]

ERROR: "Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

Dynamic Query to Microsoft SQL Server DB:

selectedLocation = Location points to a query-driven parameter list in Power BI. I want to pass the parameter user selection to the below M Query (DYNAMIC QUERY) at runtime.

let
selectedLocation = Location,
query = "select location_id, Location_Desc from location where deleted = 0",
dynamicFilter = Text.Combine({" AND Location_Desc =(","'", selectedLocation,"'",")"}),
finalQuery = Text.Combine({query, dynamicFilter}),
Source = Sql.Database("***IP***", "***DATABASE***", [Query= finalQuery])
in
Source

PRIOR TROUBLESHOOTING: Adjusted data privacy settings, which did work LOCALLY - BUT THIS IS NOT A VIABLE SOLUTION FOR PUBLISHING TO THE POWER BI SERVICE - This is only a fix for locally running the pbix file. Is there a way to adjust a privacy setting for the location paramter so that it can 'merge data' with the SQL Server connection? I am not following where the data privacy discrepancy is since this is a single data source.

Thank you!

[Anonymous]

Thanks for the reply from lbendlin.

Hi @jdc_123  , 

Have you solved your problem? If so, can you share your solution here or mark the helpful reply to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,
Zhu

[lbendlin]

Power Query M Primer (Part 13): Tables—Table Think II | Ben Gribaudo

Scroll down to the Firewall section.