How to avoid having date and date/time column (Incremental Refresh and Calendar Table)

[IvanKostov]

Hello,

I am currently building a data model, where I have various date/time columns.  In this regard I found that according to best practices I should have a calendar table. Since I don't need the times I simply converted the date/time columns in PowerQuery to date columns and the issue was resolved. However, since I also want to have an incremental refresh on the table I need a date/time column.

What are the best practices in such scenarios? Do you always need a date column and a date/time column or there are some workarounds?

[IvanKostov]

Thanks Pete! 

[BA_Pete · Accepted solution]

Hi @IvanKostov ,

If you need a date column (calendar relationship) and you need a datetime column (incremental refresh) then you need a date column and a datetime column, no way around that I'm afraid.

In Power Query, keep your datetime column as-is, then create a new custom column using the following:

Date.From([datetimeColumn])

Using Date.From will prevent query folding from breaking if your source is foldable.

Pete