Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
vfx661
Advocate I
Advocate I

Building WHERE X IN (1, 2, 3) clause with native query using dynamic parameters in power bi

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Could you describe advantage of buffering a list vs buffering a table please?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors