Dynamic Parameter in Excel PowerQuery with multiple Values

Question

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)

horseyride · Accepted Answer · 2022-03-23 11:46:02Z

1

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",

edited Mar 23, 2022 at 11:46

answered Mar 23, 2022 at 10:13

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user15401641 Over a year ago

Thank your! This helped me a lot !

Collectives™ on Stack Overflow

Dynamic Parameter in Excel PowerQuery with multiple Values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related