Forum Discussion
Helper IQuery Folding - Snowflake
Hi Anders,
When Power Query creates the native query it evaluates your query in totality and optimises it as a whole. This means, for example, that you can add a complex logic column that doesn't fold, then remove some other columns (an operation that does fold) afterwards. If your complex logic column uses data from the columns that you didn't remove in the second step then PQ will optimise this query by removing the columns first, then adding the complex column afterwards.
Similarly, if you remove your complex logic column later in your query without having used its result, when PQ evaluates the query as a whole it won't even write the creation of the column in, as it can see it's not used or retained, thus allowing the query to fold in the backend even though it may display in the step list as unfoldable.
It's always good practice to put your foldable steps first for logic and clarity, but PQ will re-evaluate and optimise the query as a whole anyway.
Pete
Super UserHi Anders,
When Power Query creates the native query it evaluates your query in totality and optimises it as a whole. This means, for example, that you can add a complex logic column that doesn't fold, then remove some other columns (an operation that does fold) afterwards. If your complex logic column uses data from the columns that you didn't remove in the second step then PQ will optimise this query by removing the columns first, then adding the complex column afterwards.
Similarly, if you remove your complex logic column later in your query without having used its result, when PQ evaluates the query as a whole it won't even write the creation of the column in, as it can see it's not used or retained, thus allowing the query to fold in the backend even though it may display in the step list as unfoldable.
It's always good practice to put your foldable steps first for logic and clarity, but PQ will re-evaluate and optimise the query as a whole anyway.
Pete