I am in similar situation like this , i have huge DB that i can not download all together so need user to be able to select parameters and then get data ccordingly , how did you approach it ? Can you please help me here .
All the tutorials and posts I saw were about how to pass the query parameter in the 'M' query in Power BI advance editor and nothing about how to actually pass a parameter by editing the souce query SQL statement.
Any idea about that?
The main problem with adding the parameter in the Advanced Editor is that, once its edited there, then the setting wheel next the Source of the query disappears and there is no way to see or edit your SQL statement after that.
Step one is creating your custom SQL statement. I like to add my placeholder for a parameter when I create the SQL statements and replace those with the parameter once defined. The parameters need to be concatenated to the query using & outside of the quotes holding the query. If you created a table with a single SQL query, your M may look like the following.
let Source = Sql.Database("MyTestServer", "MyTestDatabase", [Query="SELECT test_key from MyFactTestTable WHERE datekey >= 20190101 AND datekey <= 20190201"]) in Source
let Source = Sql.Database("MyTestServer", "MyTestDatabase", [Query="SELECT test_key from MyFactTestTable WHERE datekey >= " & StartDate & " AND datekey <= " & CloseDate"]) in Source