Incremental Refresh for Column of Type Date

[Chris99]

I am trying to configure Incremental Refresh in the desktop. However I seem to have immediately hit an issue which seems to limit it's usability. I am using SQL Server views to reduce the data exposed to Power BI, and as per best practices there are no DateTime columns in the data model as this would massively increase the size of the data model. Instead all such values in the model are of type Date.

If I understand it correctly, Incremental Refresh only works with DateTime for both the parameters and the data model columns. Is there a way of being able to apply the RangeStart and RangeEnd parameters to a Date column?

Thanks,

Chris

[v-diye-msft]

Hi @Chris99 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[Greg_Deckler]

@Chris99 Can you just create a view where you add an additional column that duplicates your original date column and and cast it to date/time? Or, this:

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

While the data type of the parameters must be date/time, it's possible to convert them to match the requirements of the datasource. For example, the following Power Query function converts a date/time value to resemble an integer surrogate key of the form yyyymmdd, which is common for data warehouses. The function can be called by the filter step.

(x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

[Chris99]

Hi @Greg_Deckler 

Thanks for this. I'm loathe to add an additional column that duplicates the Date column, but this is a route I could explore.

How do I convert the parameters from DateTime to just Date? I'm not sure where you apply PowerQuery to their use?

I'm currently sidelined on other work, but hope to return to this soon.

Chris

PS @Microsoft : Can you please add support for other data types to Incremental Refresh, or at least support conversion of source data type to DateTime if there is no loss of data?

[amitchandak]

@Chris99 , Have tried configuration with date as data type?

[Shivarajaprasad]

HI can you plrase help I am also unble to apply incremental refresh for date column
Can you please help in setting up