top of page
  • Writer's pictureDataZoe

DateID compare to DateTime in Power Query for Incremental Refresh

Incremental refresh requires a DateTime field and many tables come from the database instead with the DateID column. Here is how to convert that in Power Query.

Add formula to convert the RangeEnd/RangeStart to DateID in the filter step

  1. Go to New Source --> Blank Query

  2. Name it ConvertDateTimeToDateID

  3. Go to Advanced Editor

  4. Paste in the following: let Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x) in Source

  5. In your filter step change RangeEnd to DateTimeToDateID(RangeEnd) and RangeStart to DateTimeToDateID(RangeStart).

This comes from the documentation on setting up incremental refresh but it wasn't clear to me how to apply it right away.

How has the experience been with incremental refresh for you? I found it to be awesome when it works as expected, and painfully difficult when it doesn't work as expected!


142 views0 comments

Recent Posts

See All
bottom of page