Forum Discussion
Building WHERE X IN (1, 2, 3) clause with native query using dynamic parameters in power bi
Buffering a table brings a whole table into memory, which is a huge waste of resources, especially if all you need are the unique values in a column to use in your WHERE clause. Buffering a list allows your query to use the list in a WHERE [SQLColumn] IN clause. So in your case, use List.Buffer by wrapping it around your Text.Replace function. This should be its own query so that the result is just the unique valued, buffered list.
In fact, if you make a uniqe list as its own query, and use it in
Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(ListName), [ColumnNameToFilter]))
then you don't have to bother with writing the native query, because a buffered list in List.Contains for Table.SelectRows folds to the SQL source.
--Nate
Thanks - see the update - I've found Table.Buffer makes it work, but it also seems to make the whole query extremely slow. I'm looking for a middle ground, where only the string gets evaluated before the native query gets folded, instead of it trying to fold the query and the function.
Thanks for the link. I've also updated the function adding the transformations from list to string.