Forum Discussion
Pivote columns refresh error
Buffering doesn't affect folding. It affects speed. It gathers all of the items in the list at once, then sends the buffered list to the SQL server in the IN clause.
If you don't buffer, it streams the data into the IN clause and takes significantly longer.
As a result, folding or not, I always use a buffered list for List.Contains. And yup, Anonymous taught that trick to me.
Actually, if you are filtering your SQL with a list of values from Sharepoint, you can preserve query folding if you buffer the list:
= Table.SelectRows(TableName, each List.Contains(TableToFilter[ColumnName], List.Buffer(SharepointTable[ColumnWithFilterValues])))
Buffering the list will also make your query very fast, as far as the filtering.
--Nate
Anonymous ,
Interesting point, I wasn't aware of this.
Do you know how the native query handles the buffered table in terms of sending that step to the server?
Pete
Yessir, it sends the list as an IN clause, like
Customer ID IN (A12345, A42956, A23584, A65485, A46584).
--Nate
Beautiful. Thanks 🙂
Just tested this and sharing findings for future generations of PBIers:
After much confusion, realised the arguments were the wrong way round in the List.Contains function. It should be:
List.Contains(filterValuesTable[columnWithFilterValues], [columnToActuallyFilter])Also discovered that the list doesn't need to be buffered to preserve folding - PBI is smart enough to generate the IN clause either way:
However, recommendation to buffer the list for performance gains is very valid.
Pete
Darnit! I still get those reversed sometimes! Yes, the FilterValuesList first, then the TableColumnToFilter second.
I'm usually never at the PC when I write these comments...
But yeah, using a buffered list in a List.Contains Table filter makes filtering extremely fast. And the fact that it preserves query folding is also pretty seriously awesome. It can handle a few thousand values, you just have to make sure that your resulting SQL statement doesn't exceed the query character limit, which I can't remember.
edhans has an article about using List.Contains on his site; I'd already been using it for a while, and I casually made a comment on one of Ed's posts on Twitter about buffereing the list. He, being an actual professional, tried it, measured the performance gains, and added an addendum to the article here https://www.ehansalytics.com/blog/2020/5/20/using-listcontains-to-filter-dimension-tables
His article is much more thorough than the stuff I post on here!
--Nate