Incremental refresh with numbers as date

[joshua1990]

Hello everyone!

We have a fact table that contains a number to display the date.

The format is CYYDDD. For instance 120001 = 01.01.2020.

I already have a Power Query function to convert this number into a Date, but this would break the query folding, right?

Since the query folding is broken then, is there any chance to get an incremental refresh?

[GilbertQ]

Hi there

I have this blog post where I explain how you can still use the Key and not break incremental refreshing

https://www.fourmoo.com/2020/03/11/how-to-configure-incremental-refreshing-in-power-bi-with-datekey-...

[Nimai123]

Hello @joshua1990 

Yes, when you apply this transformation your query folding will break.

According to the  below paragraph from microsoft the source should support query folding, its ok if your file doesnt, incremental refresh will work fine.

Given the various levels of query folding support for each datasource, it's recommended that verification is performed to ensure the filter logic is included in the source queries. To make this easier, Power BI Desktop attempts to perform this verification for you. If unable to verify, a warning is displayed in the incremental refresh dialog when defining the incremental refresh policy. SQL based data sources such as SQL, Oracle, and Teradata can rely on this warning. Other data sources may be unable to verify without tracing queries. If Power BI Desktop is unable to confirm, the following warning is displayed. If you see this warning and want to check that the necessary query folding is occurring, you can use the Query Diagnostics feature, or trace queries received by the source database.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[joshua1990]

@Nimai123 : Thank you! 
Since I am using the oracle connection, it should work, right?

is there any chance to check if the incremental refresh is working properly ?

[Nimai123 · Accepted solution]

Hello @joshua1990 

Oracle connection is fine.

The only way to find that the incremental refresh is working fine is to, try to refresh your dataset manually or scheduled and see if it completed in lesser time than usual.

Have a look at this Video 

Regards,

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!