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 🙂
