Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I'm using a dyamically created list, e.g. {1, 2, 3} to build a query with a WHERE...IN clause so it only brings in data with column X = 1, 2, or 3 from snowflake
SELECT * FROM my_table
WHERE X IN (1, 2, 3)
This hard coded query works fine.
The problem is that it doesn't work dynamically and makes a folding error:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.
I've narrowed it down to the creation of the list string: "1, 2, 3".
If created dynamically it fails.
list_string_dynamic = my_function(my_parameters),
list_string_hardcoded = "1, 2, 3",
This doesn't work:
qry = "SELECT * FROM my_table WHERE X IN (" & list_string_dynamic & ")",
This works:
qry = "SELECT * FROM my_table WHERE X IN (" & list_string_hardcoded & ")",
I've checked and this is true:
check_equal = list_string_dynamic = list_string_hardcoded,
So it seems that calling the function is causing the error. Perhaps there is a way to tell power query to evaluate the function first before creating qry?
More detail on my_function: given a parameter value, it looks up another column in a table for related values and creates a distinct list of values. For example, given a parent, it looks up all the distinct children. Here's the implementation if this is relevant:
get_children = (tbl, parent_col, parent_value, child_col) =>
Text.Replace(Text.TrimEnd(Lines.ToText(
List.Transform(
List.Distinct(
Table.Column(
Table.SelectRows(tbl, each Record.Field(_, parent_col) = parent_value)
, child_col
)
, each Number.ToText(_)
)
, " ")), " ", ", ")
),
I've also checked that the output of this function matches with a hard coded list, and that the hard coded list also works and it does.
Updated: I've discovered the function Table.Buffer() and it does work - however it makes the query very slow - it updates much faster in the power query window of power bi than in power bi itself. Users will be really frustrated with the speed, so is there an alternative approach?
Solved! Go to Solution.
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
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
Don't buffer the table--buffer the list!
Wherever your complete list is created, but before you use it in the native query, use List.Buffer(list). I assure you this will speed your query right up.
--Nate
Provide more details about my_function(my_parameters) - it should not return a list but a concatenated string.
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.