Pass Sample size in SQL Query

[Anonymous]

I use the below SQL query from Power BI. Instead of manually giving in the sample size as 30, is there a way to dynamically pass the user input as the sample size.
for example, if I have a parameter, wher user can give in the number of rows, Can I pass this value inside the Advanced Editor as the sample size?

SELECT DISTINCT event_id, event_date

FROM my_table TABLESAMPLE(30 ROWS) REPEATABLE(1)

[Greg_Deckler]

@Anonymous Yes, you can use a parameter for that.

[Anonymous]

@Greg_Deckler  Could you please show how the parameter can be passed inside the SELECT query?

[Greg_Deckler · Accepted solution]

@Anonymous Sure, create a Text parameter. In my case I called mine Rows. Make sure it is Text. Then you can edit your query like the following:

let
    Rows = Rows,
    Source = Sql.Database("localhost\MSSQLSERVERDEV", "AdventureWorksDW2019", [Query="SELECT DISTINCT AccountKey, ParentAccountKey FROM DimAccount TABLESAMPLE(" & Rows & " ROWS) REPEATABLE(1)"])
in
    Source

So, the first line grabs the Rows parameter and put it in the query as a value called Rows. That is then used in the SQL Query by appending it inside the query.