Improvement to integration between Power BI Desktop Incremental Refresh policy and PBI Service

[Element115]

ISSUE:

Currently, when developing a report in PBI Desktop, if one wants to set up an Incremental Refresh policy for the Service to refresh the semantic model more efficiently, as opposed to a full refresh since the beginning of time, then all non-folding steps in the M or Power Query queries that create the tables in your model, and for which you want to enjoy incremental refresh, need to be removed.  

This is an arbitrary barrier to productivity and efficiency. 

This is true as of 2025-05-23.  

IMPROVEMENT:

The Power Query Editor and the M functional programming language is a great tool to perform ETL/ELT.   And any non-trivial work will require the user to rely on non-folding function calls whether because it is not possible to achieve in SQL or just plain too hard or cumbersome and it is more elegant to do it in M.  Or perhaps simply because you inherit the job from someone else, and the architecture that is in place cannot be changed by you only and/or easily, or a slew of other reasons. In other words, the user is stuck with what she has been given.  

No problem though, because M can do it all.  But this means that you need to avail yourself of a bunch of non-folding functions. And now you are stuck.  You can't set up an Incremental Refresh policy in PBI Desktop because the Service initiated refresh will just keep on failing.

THEREFORE (assumption is that the data source is a lakehouse table or file, or a data warehouse or SQL Server)  -->  Microsoft implement an engine that is smart enough to understand that all it has to do is:

1__grab the data from source according to the defined refresh range; this is just a matter of using a filter on a datetime column;  this will be the raw data;

2__pass this data into a M engine that will perform the transformations as per the user M code, as the desktop M engine already does;

3__store the output into the semantic model, which is now refreshed accordingly. 

I mean, this is just such a simple workflow, it is hard to comprehend why it is not possible atm to do this.  Why are users forced into an either/or posture: either all steps in a M query fold, or they don't and you are out of luck?

[SHASHANK_CCSU]

Your proposal highlights a critical pain point in Power BI's Incremental Refresh functionality, and your suggested improvement is both practical and impactful. The current limitation, where non-folding M query steps prevent efficient Incremental Refresh in Power BI Service, is indeed a significant barrier to productivity, especially for complex ETL/ELT workflows. As you rightly point out, M's flexibility is one of its greatest strengths, allowing users to handle transformations that are impractical or impossible in SQL, or to work within inherited architectures. Forcing users to eliminate non-folding steps to enable Incremental Refresh feels like an unnecessary trade-off, punishing those who leverage M's full capabilities. Your suggested solution—having a smart engine that filters data by the defined refresh range, processes it through the M engine as done in Power BI Desktop, and stores the output in the semantic model—is elegant and logical. It aligns with the principle of making tools work intuitively for users without imposing restrictive workarounds. Implementing this would not only enhance efficiency but also make Incremental Refresh accessible to a broader range of use cases, particularly for lakehouse, data warehouse, or SQL Server sources. This is a compelling case for Microsoft to prioritize, as it would significantly improve the Power BI experience for advanced users and align with the platform’s ethos of empowering data transformation. Great suggestion!