use parameters in sql query to import data

Check if these can help:

https://community.powerbi.com/t5/Desktop/Using-Advanced-Query-and-parameters-to-create-dynamic-SQL/m-p/914235

https://community.powerbi.com/t5/Desktop/SQL-parameters-in-direct-query/m-p/248180

https://community.powerbi.com/t5/Power-Query/parameters-to-SP-or-Sql-query/m-p/57734

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

Well, you can set the parameter and use it in your query. For example I use a paramenter to switch all my datasources from test/production db:

Origin = MySQL.Database(server_port, "datawarehouse", [ReturnSingleDatabase=true]),

and server_port is a parameter that I change once.

However I'm not aware of any method to force the db to refresh when you change a parameter. You can change the parameter and then force - manually - a refresh data.

santiagomur Power BI uses a model-first approach so unless you go to the query editor, you cannot change the scope of the loaded data. Consider connecting to the DB using Direct Query then.

Hi santiagomur,

I know you can do this with dates as this is a problem I struggled with for a while when I was implementing incremental refresh for one of my reports.

The way I got it working for Import Mode was:

• In Power BI, set up 2 Date/Time parameters. One for your start date and another for your end date (It sounds like you might have already done this)
• In your SQL query, DECLARE 2 variables/paramteters as varchar(19) and another 2 variables as date. E.g.

DECLARE 
@RangeStart as varchar(19), 
@RangeEnd as varchar(19), 
@ReportStart as date,
@ReportEnd as date

• In SQL, set your RangeStart and RangeEnd variables with arbitrary long date time values, and set the ReportStart and ReportEnd variables as a conversion of these to datetime. Use ReportStart and ReportEnd in your WHERE clause:

SET @RangeStart = '2020-01-01 00:00:00'
SET @RangeEnd = '2020-01-02 00:00:00'
SET @ReportStart = CAST(@RangeStart as datetime)
SET @ReportEnd = CAST(@RangeStart as datetime)

• Once this query is in Power BI, click Home -> Transform Data, then click "Source" under the "Applied Steps" pane
• In the query bar, find the part of your query that SETS your @RangeStart and @RangeEnd parameters. Replace the date with

SET @RangeStart = '"&DateTime.ToText(Parameter1,"yyyy-MM-dd hh:mm:ss")&"'
SET @RangeEnd = '"&DateTime.ToText(Parameter2,"yyyy-MM-dd hh:mm:ss")&"'

When you refresh your data, your Power BI paramteter values should now be passed to the SQL query.

Cheers,

Andy