Why Doesn’t Power Query Know If My Step Will Fold?

Recently, when preparing my session on query folding in Power Query for SQLBits, I wrote the following query to demonstrate the “Might Fold” indicator in Power Query Online:

let
  Source = OData.Feed(
    "https://services.odata.org/TripPinRESTierService", 
    null, 
    [Implementation = "2.0"]
  ), 
  Navigation = Source{[Name = "People", Signature = "table"]}[Data], 
  #"Filtered rows" = Table.SelectRows(Navigation, each [FirstName] = "Angel")
in
  #"Filtered rows"

This query connects to the TripPin OData feed (which is public, so you’ll be able to try this yourself) and filters the People table so it only gets the rows where the FirstName column equals “Angel”. If you paste this query into the Advanced Editor in Power Query Online to create a dataflow, you’ll see that the filter shows the “Might Fold” step indicator:

This tells you that Power Query doesn’t know if the filter on the FirstName column folds or not. The Query Plan view shows two alternate plans for if the query folds and if it doesn’t:

The question is, though, why can’t Power Query tell if the query will fold? I didn’t know so I asked Curt Hagenlocher of the Power Query development team, who very kindly explained.

It turns out that OData sources (and also ODBC sources) sometimes misreport their support for query folding. At the time that the query plan above is generated Power Query has already asked the source for its list of supported OData capabilities, but it won’t be until the query is actually run that it will know for sure if the filter can be folded. Similarly, some OData sources have an undeclared maximum URL length limit which means that if Power Query exceeds that it may get an error or even incorrect data back. As a result of this the runtime behaviour of Power Query has several fallbacks: it will try to fold fully, then fold some transforms, then fold nothing. All of which explains why the “Might Fold” step indicator exists.