Query Folding

You can read about query folding in detail in official documentation and the resources I have provided below. But at a high level, query folding allows us to create query steps that send a single native query back to the data source instead of the mashup engine doing the transformations. As much as possible, we want to design queries so they fold. Not all PowerQuery transformations are foldable. One of those transformations is changing data type.

%%html

<iframe title="vimeo-player" src="https://player.vimeo.com/video/714177719?h=466f2d1c1b" width="640" height="360" frameborder="0" allowfullscreen></iframe>

Checking Query Folding

It is still debatable whether changing data types actually breaks query folding. My own tests using SQL Server, Postgres (BigQuery, Bit.io), OData, Synapse Analytics Serverless/Dedicated servers show that most data type changes don't actually break folding when you check the server logs. But my friend Nikola recently pointed out that it may not always be the case. There are three ways to check if a query is folding :

  • Right click the step in PQ and check if View Native Query option is greyed out. If it's greyed out, folding has stopped. Below query is foldable as the option is not greyed out. However, If this option is greyed out it doesn't necessarily mean that query folding isn't happening. But if it is not greyed out then we can be sure that query folding is taking place for sure. This method is similar to using Value.Metadata() to check query folding (link).

  • Use Start Diagnostics in Power Query and check the detailed diagnostics report for the query sent to the data source. You can watch my video above to learn about this technique. This process is definitely a bit tedious. You can also use SQL Profiler in DAX Studio or SSMS to trace queries and identify the queries generated.

  • Check the server logs. This is the most straightforward and sure fire way. But not everyone has access to these logs.

Changing Data Type

So how do we make data type changes, query folding safe? Easy - Use Table.TransformColumns() instead of Table.TransformColumnTypes() .I have verified this with all the foldable data source types and it has always worked. If it doesn't work in your scenario, please let me know. In the screenshot below, I am connected to serverless SQL pool in Synapse Analytics. I will be changing data types for three columns UnitPrice (decimal), UnitPriceDiscountPct (decimal), Is_Promotion (text).

Decimal to Percentage

Custom Transformation :

'Table.TransformColumns(Data, {{"UnitPriceDiscountPct",Number.From, Percentage.Type}})'

Decimal to Currency

Custom Transformation :

'Table.TransformColumns(Data, {{"UnitPrice",Number.From, Currency.Type}})'

Text to Logical

Custom Transformation :

'Table.TransformColumns(Data, {{"Is_Promotion",Text.From, type logical}})'

You can use the exact same pattern to make any data type change query folding safe, including for text and date columns.

'Table.TransformColumns(Data, {{"ColumnName",<..>.From, type <enter type>}})'

Summary

Data type changes do not always stop query folding. You have to check the server logs to ensure if it's truly folding or not. But you can use above described method to always force query folding. This method works for SQL server, OData and Postgres SQL.

Resources