Forum Discussion
Need help with parameter in SQL statement
Hi everybody,
I have a simple statement. In the statement below Search is the parameter I made. When I fill custom criteria the statement works fine. When I change the custom criteria it also works fine. But when I enter the parameter search, nothing happings. Tried a lot of solutions, youtube, forums etc. but i can't simply figured out what i'm doing wrong.
SELECT [PointName]
,[PointID]
,[PointSliceID]
,[UTCDateTime]
,[ActualValue]
FROM [DataBase].[dbo].[RawAnalog]
WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())
AND PointName LIKE '%Search%'
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
9 Replies
Create a measure like this and add it to table or matrix along with [PointName],[PointID],[PointSliceID],[UTCDateTime]
new ActualValue = calculate(sum(RawAnalog[ActualValue]),RawAnalog[UTCDateTime] >= date(year(today())month(today())-1,day(today())), search("Search",RawAnalog[PointName] ,1,0)>0)Thanks for your reply, maybe I am not clear enough. The meaning is to import only the data that is needed, so search must contains some characters from Pointname. When search contains '%United States%' only pointnames with United States must be imported. All this in a template. When I only use the IP address as a parameter all works fine, by adding the second parameter (Search) it shows up by making a connection but doesn't do the job well.
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.
