[ Behavior ] Depending on the order of the transformation steps, this can result in a subquery that requires a full scan. // Narrow down the columns at the end M Query : #"Filtered Rows" = Table.SelectRows(SalesLT_Product, each ([Color] = "Black")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ProductID", "Name", "Color"}) Native Query : select [_].[ProductID], [_].[Name], [_].[Color] from [SalesLT].[Product] as [_] where [_].[Color] = 'Black' and [_].[Color] is not null // Narrow down the columns at the first M Query : #"Removed Other Columns" = Table.SelectColumns(SalesLT_Product,{"ProductID", "Name", "Color"}), #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Color] = "Black")) Native Query : select [_].[ProductID], [_].[Name], [_].[Color] from ( select [ProductID], [Name], [Color] from [SalesLT].[Product] as [$Table] ) as [_] where [_].[Color] = 'Black' and [_].[Color] is not null [ Request ] This is due to the folding feature. While the goal of the folding feature has been achieved, there seems to be room for improvement in the process by which optimal queries are generated. We would appreciate it if you could improve the optimization behavior to prevent bottlenecks on the data source side as much as possible, especially for data sources within Microsoft. Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn The goal of query folding is to offload or push as much of the evaluation of a query to a data source that can compute the transformations of your query.
... View more