Why can performing a split by position on a column cause a dataflow to load more data than expected?

Why can performing a split by position on a column cause a dataflow to load more data than expected?

• A. It converts DateTime to string
• B. It increases network latency
• C. It enables dynamic query optimization
• D. It causes the data to be pulled into Power Query before filtering

Answer: (D)

Split by position can’t be folded back to the data source. In Power Query/Dataflows, query folding pushes as many operations as possible down to the source so only the needed rows are retrieved. When you split by position, the transformation isn’t something the source can apply, so the data must be pulled into Power Query first, then the split is performed and the filters are applied afterwards. That means more data is loaded into the flow than if the filtering could have happened at the source. So the reason this causes more data to be loaded is that the data is pulled into Power Query before filtering, rather than filtering at the source.

Split by position can’t be folded back to the data source. In Power Query/Dataflows, query folding pushes as many operations as possible down to the source so only the needed rows are retrieved. When you split by position, the transformation isn’t something the source can apply, so the data must be pulled into Power Query first, then the split is performed and the filters are applied afterwards. That means more data is loaded into the flow than if the filtering could have happened at the source.

So the reason this causes more data to be loaded is that the data is pulled into Power Query before filtering, rather than filtering at the source.