You can set a parameter in power query.
sqlStatement = "
select *from Transactionswhere CustomerID = " & CustomerID //CustomerID is the name of the parameter
The parameter can be changed by the user in power bi "edit parameter"
Thanks for the reply
This is the query which I'm using:
DECLARE @return_value int
EXEC @return_value = [dbo].[Stored Procedure] @startdate = &start_date , @enddate = &end_date
SELECT 'Return Value' = @return_value
It throws error saying Incorrect syntax near &"
I need to take the dates as input from the user when opening the report
I guess there is a little misunderstanding. If you want to execute sql Code (as String) in Power BI you have to insert it into M-Language Code.
And then you do not use SQL Variables, but M-Language Variables.
M Code would be:
source = Sql.Database("server","database",[Query = select * from Table1 where Columnvalue= " & NAME_OF_PARAMETER])
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!