PowerQuery - Dynamic Date Parameter

Question

I have the following PowerQuery in Excel

let
    Source = Excel.CurrentWorkbook(){[Name="Query1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Year", type date}}),
    #"Custom Step"  = Table.SelectRows(#"Changed Type", each [Month Year] = #date(2017, 12, 1))
in
    #"Custom Step"

I would like to set this up so that #date(2017, 12, 1)) is a parameter. In other words, I want to have a filter/dropdown on a worksheet and whatever the user selects (December 2017, November 2016, etc.) I want the "Custom Step" to filter the table based on the selection.

Is there some sort of syntax that will allow me to use a parameter? (kinda like @variable in SQL)

Yes, you can read in cell values to be used as parameters as seen in my answer here: stackoverflow.com/questions/49281763/… — Alexis Olson

teylyn teylyn · Accepted Answer · 2018-03-26T20:36:02

There are many different ways to get a parameter for a query. You can use a named range and access it directly or with a function, or you can even build a table with several parameters and access them with a function. Ken Puls has a good starting point for parameter tables here

With just one parameter, you can simply use a named range. In the following screenshot, cell F4 has the range name SelectedDate. It is easy to get this parameter with the line

myDate = Excel.CurrentWorkbook(){[Name="SelectedDate"]}[Content]{0}[Column1]

but the result will be text and won't filter the table correctly. You need to convert it into a date, for example by wrapping it into a Date.From function

myDate = Date.From(Excel.CurrentWorkbook(){[Name="SelectedDate"]}[Content]{0}[Column1]),

Now the variable myDate can be used to filter the table. Here is the complete M code:

let
    myDate = Date.From(Excel.CurrentWorkbook(){[Name="SelectedDate"]}[Content]{0}[Column1]),
    Source = Excel.CurrentWorkbook(){[Name="Query1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Month Year", type date}}),
    Filtered  = Table.SelectRows(ChangedType, each [Month Year] = myDate )
in
    Filtered

PowerQuery - Dynamic Date Parameter

1 Answers