CODEWITHSUNDEEP
excelfilterparameterspowerquery
user15401641
user15401641

Reputation:

Dynamic Parameter in Excel PowerQuery with multiple Values

I built a parameter table for my power query to pre-filter the data that will be loaded into my Excel. Sort of like this

This works quite well for parameters that contain single values. However, this will be not sufficient enough for parameters that contain multiple values. I would be glad for any input on how I can use multiple values for my parameters to filter my data. For example: Para1 should be 10 and 11. Or Para2 should be the date from 2020/01/01 to 2021/01/01

Here is some of the Source code:

#"Filtered Rows" = if Para1 <> null then Table.SelectRows(#"Changed Typ", each ([Value1] = Para1)) else Table.SelectRows(#"Changed typ", each true),

#"Filtered Rows1" = if Para2 <> null then Table.SelectRows(#"Filtered Rows", each ([Value2] = Para2)) else Table.SelectRows(#"Changed Typ", each true),

#"Filtered Rows2" = if Para3 <> null then Table.SelectRows(#"Filtered Rows1", each ([Value3] = Para3)) else Table.SelectRows(#"Filtered Rows1", each true)

Upvotes: 0

Views: 702

Answers (1)

horseyride
horseyride

Reputation: 21298

You could use two Para's in one filter

= Table.SelectRows(#"PriorStep", each [Value4] >= Para4 and [Value4] <= Para5)

Or put two "things" in one Para,

1/1/2022::1/5/2022

and then split them in the code

Para4_1= Text.Split(Para4,"::"){0}
Para4_2= Text.Split(Para4,"::"){1}
xx = Table.SelectRows(#"PriorStep", each [Value4] >= Para4_1 and [Value4] <= Para4_2)

Note, instead of

else Table.SelectRows(#"Changed Typ", each true),

you could use

else #"Changed Typ",

Upvotes: 1

Related Questions