Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi team,
I have a PBI file with many tables there, all of these tables come from SQL Server (DirectQuery).
I wrote SQL statements in the Advanced options part, like following:
select A, B, C, D, E, sum(sales_amt) as sales
FROM Table
where month in (concat(left(convert(varchar, dateadd(MONTH, -2, GETDATE()), 102), 4), RIGHT(substring(convert(varchar, dateadd(MONTH, -2, GETDATE()), 102), 1, 7),2))
,concat(left(convert(varchar, dateadd(MONTH, -1, GETDATE()), 102), 4), RIGHT(substring(convert(varchar, dateadd(MONTH, -1, GETDATE()), 102), 1, 7),2)))
I fetch the months 202409, 202408 data from Table.
But sometimes we want to change the month, not fetch these 2 months, so that means I have to manually change the code for each table, we have around 20 tables.
I want to pass parameter/value to this SQL code, so I only change in parameter/value, then the code in each table could be changed accordingly.
I created the values as following, now I want to pass these values to the SQL statement. Is it possible?
If yes, can you pls. advise how to write in where condition?
Thanks a lot!
Solved! Go to Solution.
Hi @Tracy000
You can right click on your query and change it into a parameter.
Then connect to SQL put your code under the Advanced section.
After your code has connected, on the right hand side under Applied steps click on Source (not the cog).
This will show your code in the M editor above your table.
Go to where you want to pass in the parameter and split the SQL script using '" & yourparameter & "' that will insert your parameters value into the code 🙂
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi @Tracy000
You can right click on your query and change it into a parameter.
Then connect to SQL put your code under the Advanced section.
After your code has connected, on the right hand side under Applied steps click on Source (not the cog).
This will show your code in the M editor above your table.
Go to where you want to pass in the parameter and split the SQL script using '" & yourparameter & "' that will insert your parameters value into the code 🙂
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi @SamWiseOwl ,
Many thanks for your reply and suggestion.
I used this format '" & yourparameter & "' in M editor without converting query to parameter, then it works.
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |