Forum Discussion
Need help with parameter in SQL statement
I think these details already Greg_Deckler has provided. these are a couple of link for the same
https://docs.microsoft.com/en-us/power-query/power-query-query-parameters
https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716
So you have a Query Editor parameter setup like the screen shot below?
Following is what I have;
SELECT [PointName]
,[PointID]
,[PointSliceID]
,[UTCDateTime]
,[ActualValue]
FROM [JCIHistorianDB].[dbo].[RawAnalog]
WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())
AND PointName LIKE '%NCE26%'
This works fine (with the parameter SQL IP Address, but the NCE26 It must be replaced by a parameter. When connecting to the database the follwing screen appears. (Search is the name of the parameter)
OK, assuming you have the parameter setup correctly, can you share at least the first few lines of your Power Query M code? Used Advanced Editor in Power Query, I mainly need to see your Source line.
let
Source = Sql.Database(#"SQL IP Address", "JCIHistorianDB", [Query="SELECT [PointName]#(lf) ,[PointID]#(lf) ,[PointSliceID]#(lf) ,[UTCDateTime]#(lf) ,[ActualValue]#(lf) FROM [JCIHistorianDB].[dbo].[RawAnalog]#(lf) WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())#(lf) AND PointName LIKE '%NCE26%'"])
in
SourceComplete copy of the source line
I think these details already Greg_Deckler has provided. these are a couple of link for the same
https://docs.microsoft.com/en-us/power-query/power-query-query-parameters
https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716
Just had to change the Power Query M code. Thanks for sharing the posts, the red-gate link gives me the solution!
Right, you should have just had to change the code to:
let Source = Sql.Database(#"SQL IP Address", "JCIHistorianDB", [Query="SELECT [PointName]#(lf) ,[PointID]#(lf) ,[PointSliceID]#(lf) ,[UTCDateTime]#(lf) ,[ActualValue]#(lf) FROM [JCIHistorianDB].[dbo].[RawAnalog]#(lf) WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())#(lf) AND PointName LIKE '%Search%'"]) in