PowerBI SQL query folding not working

[tcburge3]

Hello,

I have a powerbi report connected to a SQL server view that I am trying to implement an incremental refresh policy on. To do this, my table needs to be able to support query folding. 

This is what I'm running into:

As you can see from the first picture below, my original query supports query folding. However, for the IncrementaI refresh to work, I need to change my "max_date" column data type to be either date or date/time type. When I do this change, my query folding option no longer works (as seen by the "view native query" option being greyed out), this is shown in the second picture below.

First picture:

Second picture:

I have seen that I should try using the applied step “Table.TransformColumns()” instead of “Table.TransformColumnTypes()” to change my data type. But so far, I have not been able to get this to work as the original column is just a text column (though, it does show a date and whatnot). Here is a snip of the column im trying to change to date/time below.

Please let me know if you have thoughts on a solution to this. 

In summary, my goal is that I need to change this "max_date" column to be date or date/time, while also keeping my query folding functionality enabled in my report.

If there is any additional information I can provide please let me know and I would be happy to. Any and all help is greatly appreciated. Thank you!

[v-kaiyue-msft · Accepted solution]

Hi @tcburge3 ,

You can see from your screenshot that your date column has multiple duplicate values, which results in multiple results and power bi doesn't know which one to select, so the option will be greyed out. For more information about query folding, please refer to the document: Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn. For more information about incremental refresh, refer to the documentation: Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn.

Alternatively, you could try not relying on Power Query to change the data type, consider modifying the SQL view or creating a new view that converts the "max_date" column directly to the required data type in SQL Server. This approach ensures that the data types are correct when imported into Power BI, potentially allowing query folding to continue working.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tcburge3]

Thank you Clara! We went with the second option and made the data type change on the SQL server view. It helps to allow query folding by not having some many transformations in our power query editor. I appreciate your help on this!

[tcburge3]

Please note: some of the data in this post has been blocked out with white squares for data privacy issues. Again, if there is anything additional I can provide, i will be happy to! Thanks.