Query Folding with SharePoint Online List? RRS feed

  • Question

  • Simple question...  Is query folding supported for a SharePoint Online List source? 

    I can't find a definitive answer, specifically when using SP Online through Office 365.  I'm trying to use Excel to connect to a list and do a basic date range filter on the list with PowerQuery.  It seems Excel is pulling down the entirety of the list and then filtering within Excel.  Obviously, for large lists this is inefficient.  If folding is not supported with this Excel/SPO List combination do I have any other options for connecting SPO List data to Excel that does?


    Thursday, April 18, 2019 3:35 PM


All replies

  • Some amount of query folding is supported, but with Date Filters the situation becomes complicated. The short answer is that filtering to a fixed range of time has a good chance of folding, but filtering relative to the current date likely will not.
    Friday, April 19, 2019 7:57 PM
  • I thought that might be the case so I tried a fixed date and I also just tried filtering on string, but Excel still seemed to download several megabytes of data for just a single record.  Is there at least a definitive way to know if folding is being used?  I came across the "view native query" option, but it's greyed out no matter how simple my filter is.  Does that mean folding is not working? Or is that option just not available for SP sources?


    Friday, April 19, 2019 8:01 PM
  • No, there isn't any definitive way within Excel to check the folding. The View Native Query option just isn't available for SP; folding might still be happening under the covers. Even when folding we still require retrieving metadata from SP to function, so the download size may be larger than what you might expect (for example, we need to get the list of all SP lists in order to navigate to a specific list). If you avoid any filters and still see the same download size, that's probably a good hint that the filters were not getting folded.
    Friday, April 19, 2019 9:18 PM
  • you can use a proxy or a tool like Fiddler to figure out what are the calls being made to your SharePoint site. That way you can tell if things are being folded or not
    Saturday, April 20, 2019 2:30 AM