Dynamically allows user to filter range or multiple values

[SBM]

Hi all,

I created a parameter table to enable the final user to pre-filter data based on their input. When I try a single input (e.g. month number) it works well --> I created a function as explained by Ken Puls (Building a Parameter Table for Power Query)

What I try to do is to allow the final user(s) to select/filter based on:

• a single value (is working perfectly);
• a range (e.g month 2 till 6);
• multiple values (e.g months 2,4,6 and 8)

The parameter table is represented as follows in excel:



I tried to define them as a list {2,4,6} and {2..6} but I'm not sure how to use it afterwards for filtering my query.
With the getParameter function I have the correct input {2,4,6} , but don't know how to use it for filtering.
I tried to use List.Contains but of course my column containing the months is not a list so this generates an error.
Any idea how to easily solve this?

To summarize: I would like to replace this code and make it dynamic based on my parameter table in Excel
Multiple values:

Table.SelectRows(#"Inserted Month", each ([Month] = 2 or [Month] = 4 or [Month] = 6))

Range:

Table.SelectRows(#"Inserted Month", each [Month] >= 2 and [Month] <= 6)

Thanks for your suggestions.

Have a nice day!

 

[GraH]

Hi,

Maybe this can help you fine tuning your approach.
I would not use List syntax in Excel. So here I use the month references with a space in between.

Book1
	C	D
2	Param	Value
3	MonthRange	3 6
Sheet1

Not using any function here, but basically I use the input table and convert the parameter MonthRange to a list. This Query is called MonthRange too.

let
    Source = ParamTbl,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Param] = "MonthRange")),
    Value = #"Filtered Rows"{0}[Value],
    #"Split Text" = Text.Split(Value, " "),
    Custom1 = List.Transform(#"Split Text", each Number.From(_))
in
    Custom1

Applying this parameter in the filter function:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Month] >= List.First(MonthRange) and [Month] <= List.Last(MonthRange))
in
    #"Filtered Rows"

I hope this helps you on the way.

 

[GraH]

For multiple months it could be like this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each (not List.IsEmpty(List.Intersect({{[Month]},MultipleMonths}))))
in
    #"Filtered Rows"

Actually looking forward to some PQ wizards answering with more optimal M.