use parameters in sql query to import data

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