How to pass a value to SQL DB DirectQuery where condition

[Tracy000]

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!

[SamWiseOwl · Accepted 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 🙂

[Tracy000]

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.