Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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%'
Solved! Go to Solution.
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)
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
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
Source
Complete copy of the source line
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |