Types and Folding within Power Query

This appears to have changed since I posted this and now it will query fold when types are changed in either manner.

I recently found this trick to improve folded queries when changing data types. It allows you to do type changes of columns without breaking the query folding. You can see if a step has been folded by right clicking on the step, if “View Native Query” is an option then the step has been folded. (Note that the opposite is not necessarily true, some sources do support some query folding yet do not allow you to see/edit the native queries.)

For example an I have a local SQL server running the AdventureWorks database and I add a column that just doubles the SalesAmount column. This new column was folded down into the source this can be verified either by viewing the native query within Power BI, or by connecting to the source with profiler. This will show you the query shown below.

select 
    [_].[ProductKey] as [ProductKey],
    [_].[SalesOrderNumber] as [SalesOrderNumber],
    [_].[OrderQuantity] as [OrderQuantity],
    [_].[SalesAmount] as [SalesAmount],
    [_].[TaxAmt] as [TaxAmt],
    [_].[SalesAmount] * 2 as [Custom]
from 
(
    select [ProductKey],
        [SalesOrderNumber],
        [OrderQuantity],
        [SalesAmount],
        [TaxAmt]
    from [dbo].[FactInternetSales] as [$Table]
) as [_]

As you can see the new custom column appears in the SQL query. After creating a column a common next step is to set the column type, in this case to currency aka fixed decimal. So far so good, however if I was to do another step which is normally folded. Such as filtering the table by this new custom column you can see with profiler that the query to the source remains unchanged.

This is because the change type step breaks the query folding, however there is a way to fix this. The trick is within Table.AddColumn(), its third argument allows you to specify the type of the column, this takes the form of “[Type].Type” e.g. Currency.Type, Date.Type, Int64.Type etc. When this method is used for type conversion query folding still works. You can see this below where filtering by the new custom column gets folded.

select
    [_].[ProductKey],
    [_].[SalesOrderNumber],
    [_].[OrderQuantity],
    [_].[SalesAmount],
    [_].[TaxAmt],
    [_].[Custom]
from 
(
    select 
        [_].[ProductKey] as [ProductKey],
        [_].[SalesOrderNumber] as [SalesOrderNumber],
        [_].[OrderQuantity] as [OrderQuantity],
        [_].[SalesAmount] as [SalesAmount],
        [_].[TaxAmt] as [TaxAmt],
        [_].[SalesAmount] * 2 as [Custom]
    from 
    (
        select 
            [ProductKey],
            [SalesOrderNumber],
            [OrderQuantity],
            [SalesAmount],
            [TaxAmt]
        from [dbo].[FactInternetSales] as [$Table]
    ) as [_]
) as [_]
where [_].[Custom] > 1000

If you are looking for any more info on query folding I recommend this post on mssqltips.

• Power-Bi
• Power-Query
• Tips-Tricks