Ravindra BagaleCourses & study guides

10. Parameters in Power Query (and Other Kinds of Parameters)

10.5 Date-range Parameters for Filtering

By Last updated:

Loading 5 years of orders on a training laptop is slow. A StartDate parameter limits the rows, and with SQL sources the filter folds into the SQL WHERE clause.

Steps in Power BI

  1. Create StartDate (Type Date, current value 01-01-2025) and optionally EndDate.
  2. In Orders, click the Order DateTime filter arrow › Date/Time Filters › Custom Filter (or Is After or Equal To). In the value box click the drop-down › Parameter › StartDate.
  3. Because Order DateTime is Date/Time and StartDate is Date, adjust the formula (below) to compare like with like.
  4. Right-click the step › View Native Query to confirm the WHERE clause (Module 4.4).
Filtered = Table.SelectRows(Orders, each
              [OrderDateTime] >= DateTime.From(StartDate) and
              [OrderDateTime] <  DateTime.From(Date.AddDays(EndDate, 1)))

Tip

A DevMode True/False parameter can switch between a small sample and the full data: if DevMode then Table.FirstN(Filtered, 5000) else Filtered. Remember to set it to false before publishing.

Ravindra Bagale's Tip

Friends, many students filter with a date parameter on a text column and get no rows or errors. Make sure the column and the parameter have the same type (Date vs Date/Time). For incremental refresh, use Date/Time and filter with >= RangeStart and < RangeEnd so no row is counted twice. Try it once more, then move on.