SQL Query with Parameter

[GrahamR99]

Hello

I'm trying to put a paramenter in my SQL Query so I have change the code to this.

let
    Source = Sql.Database("FRV", "DW", [Query="Select *#(lf)from [dbo].[FactRetailSales] FRS#(lf)Inner Join [dbo].[DimRetailProduct] DRP on FRS.Product_ID = DRP.Product_ID#(lf)Inner Join [dbo].[DimDate] DD on FRS.Sale_Date = DD.DateKey#(lf)Where DD.Year = '" &Year_Select&"'"])
in
    Source

Year_Select is my parameter.

However I am getting this error.

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

Does anyone know how I resolve the problem?

Regards

GrahamR99

[v-huizhn-msft]

Hi @GrahamR99,

The error message means there is a problem about Query 'Sales_Data', please follow the steps here to troubleshooting it. From your statement, the passed paramenter statement syntax is right. You can check how to Pass parameter to SQL Queries statement using Power BI in this vedio.

In addition, I post two similar articles for your reference.

Formula.Firewall: Query references other queries, so it may not directly access a data source. 
How do I pass parameters to my SQL statement?

Best Regards,
Angelia

[GrahamR99]

Hello Angelia

I have followed how to set a parameter video, I still get the same issue.

The trouble shooting page is not much help as I am querying SQL server not Excel and my parameter is in the first line of the SQL code.

Do you have any more help?

Regards

GrahamR99