Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Converting text to date breaks query folding in Sap BW source



I'm trying to convert a text date to datetime so that I can enable incremental refresh on a report sourced from SAP BW4. When I do the last step of converting the text date to date query folding breaks and disables the possibility of incremental refresh.


So far I have tried:

1. Using the Home - Transform - Data Type: DateTime button. No change, breaks query folding

2. Editing the M query with Date.From and DateTime.From on the specific text field instead of a seperate step. No change, breaks query folding.  

3. Change the source query to create a datetime field in SAP BW - not possible in my organisation as the entire BW is frozen for query edits and transformations can only occur in the next layer due to historical security issues.


Is there any sort of workaround I can do to enable this? It could save the org hundreds of hours of query refresh time.





Frequent Visitor



It's a straight up query from a SAP BW database but where the data fields are in the format 'SEP 2023' for example. 


My belief was that even if I do enable incremental refresh on a none-folded query it doesn't really work behind the scenes, for example:

So it would activate but provide no gains, meaning I'm still looking for an answer to my original query if possible. 

That video only shows that the customer didn't understand how Incremental Refresh works in the service.  In their scenario the filtering needs to happen in the native query.  Then it will work, despite not folding.


You need to keep in mind that the ultimate goal of incremental refresh is to flush and fill a partition. Nothing more, nothing less.


Please show a sanitized version of your M code.

I think you are answering a different goal - my goal is to use incremental refresh and obtain it's performance gains in refresh time not just do it for it's technical description of flushing and filling a partition. I also want the benefit of quicker refresh times and that is what I would like to emphasise. 


It sounds like you sare saying if I implement a non query folded incremental refresh it will not provide the refresh performance time gain that I am after, therefore I will not continue here.


As if now then my belief is that with a text date it is not possible to gain any performance improvement by using incremental refresh. I will end the investigation here unless anyone has an example where they have done it and can post about it, thanks. 


my goal is to use incremental refresh and obtain it's performance gains in refresh time 


You can specify partitions down to day level.  That should keep your refresh time down.


The real issue with incremental refresh is that it is incremental. What most business scenarios require is differential refresh or CDC. You need to do extra work to accomplish that with the given Microsoft features.

Super User
Super User

query folding breaks and disables the possibility of incremental refresh.

That is incorrect.  While Query folding is desirable, it is not required for incremental refresh.


Please show a sanitized version of your M code.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors