Forum Discussion
How do I build report against sql query against SQL Server database and Pass parameters to query
Hi vijaycp2 ,
as parry2k suggested ,to make your query dynamic you can define one or more parameters in Power Query (via Manage Parameters) and then reference those parameters inside your query using M code. For example, using the Value.NativeQuery function you can build a query string that concatenates your parameter’s value.
let
Server = Sql.Database(“YourServer”, “YourDatabase”),
ParamValue = ParameterName,
Query = “SELECT * FROM YourTable WHERE SomeColumn = ‘” & ParamValue & “’”,
Data = Value.NativeQuery(Server, Query)
I referred to this link - Create dynamic reports with parameters - Training | Microsoft Learn however I am getting following error for the simple following query used. DataSource.Error: Microsoft SQL: Incorrect syntax near '&'.
Query -
SELECT HRRef, FirstName, LastName
FROM dbo.HRRM
WHERE HRRef = &HRRef -- I tried HRRef = '&HRRef&' however same error
