Need help with parameter in SQL statement

[Martin74]

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%'

[Greg_Deckler]

So you have a Query Editor parameter setup like the screen shot below?

[Martin74]

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)

[amitchandak · Accepted solution]

I think these details already @Greg_Deckler has provided. these are a couple of link for the same

https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-b...

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

[Martin74]

Just had to change the Power Query M code. Thanks for sharing the posts, the red-gate link gives me the solution!

[Greg_Deckler]

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

[Greg_Deckler]

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.

[Martin74]

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
Source

Complete copy of the source line

[amitchandak]

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)

[Martin74]

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.