ListName from parameter

[taskob]

Hi All,
I would like to use a Parameter (List_Param) instead of hardcoding the name of my list to filter out some of the rows from several tables in the model.

#"Remove Rows" = Table.SelectRows(#"Type modified", each try List.Contains(Element_list1, [ElementCode]) otherwise null = true)

I tried to replace Element_list1  with 

Expression.Evaluate(List_param, #shared) 

  -(List_param is set to Element_list1)- and it filters the rows in query editor, but when I close the query editor and refresh the data, my table will be empty.

How can I use my parameter List_Param to filter out rows from my table that aren't in the list named Element_list1?

Thanks!

[PwerQueryKees]

I do not know your use case, but there may be alternatives to Expression.Evaluate() you may want to consider, because Expression.Evaluate() sometimes causes security errors:

• If you list is a column of a table you could use Table.Column to access it by name
• If your list is a variable in your function, instead of assigning the name of the list to the parameter, assign the list to it.
• if your list(s) are separate queries, combine them in 1 record that can be a sperate query.

[
   list 1 = {1,2,3},
   List 2 = {"a", "b", "c"}
}​

Acces any individual list with Record.Field

Hope this helps.

[dufoq3]

Hi @taskob, it is possible, but we don't know how do you store your lists for such parameter. Do you store them as a separate queries? 

You can do it this way for example. Check attached .xlsx file!

[taskob]

I see @dufoq3.  My version seems to work in Excel too (see attached) , but i need it to work in Power BI Desktop and Cloud too....
List_param.xlsx  

[dufoq3]

It works the same in Power BI Desktop. 

[taskob]

If you list the data from Element_values in a visualisation, it works for you? For me it's ok in Query editor, but no data in the visualisation.

[dufoq3 · Accepted solution]

You are right @taskob, but you can do it this way (it works also in Power BI)

I've hardcoded your lists into a table. See attached .pbix file.

[taskob]

Thank you!

[dufoq3]

You're welcome, enjoy 😉