Incremental refresh for dataflow takes as much time as full refresh

[sarahshh]

Hello,

I've used incremental refresh for my dataflow, but it takes almost as long as the full refresh.

He're below is the configuration and the timings:

for the full refresh (~15min):

for incremental refresh (~15min):

Do you know why ?

Thanks !

[Rigensis]

From your screenshots it looks like the Incremental refresh, at least in the way it is set up now, is not actually loading a portion of the data during incremental load.
As we see in your screenshots, in both full and incremental refreshes the 'Rows Processed' column shows pretty much the same number of rows.
This could be, as @Deku mentions, due to query folding not working. Or it could be that the date field to which you have set the incremental load is affecting a lot more rows that you initially expect.

[sarahshh]

How do I know if the query is folding ? here's what I see in the dataflow editor:

[Deku]

The red green colour strip indicates query folding

https://learn.microsoft.com/en-us/power-query/step-folding-indicators

You can see your second step breaks folding as it has a red indicator

[sarahshh]

thank you, I believe it's because i'm using a custom select. I searched online and the only thing I found to enable query folding with custom select is to add a step with Value.NativeQuery(...) and put the sql query inside

is there any other way to enable query folding apart from this workaround ? this workaround doesn't seem to allow the use of ctes.

[Deku]

not sure what you mean by custom select

[sarahshh]

this is what i meant:

[Deku]

Have you applied the folding flag on the native query like this?

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development' ", null, [EnableFolding = true])

[sarahshh]

yes, I did, that's the workaround I found online instead of putting my sql query in the advanced options. I was asking if there is any other way to enable query folding as i can't wrap a sql query containing CTEs in Value.NativeQuery(Source, ...).. it gives me an error

[Deku · Accepted solution]

Can you define a view in the you SQL source that defines the query, including the CTEs, that you call in your data flow?

[sarahshh]

not a bad idea, i will do this then, thank you ! 🙂

[Deku]

Are you sure the query is folding?