• DataZoe

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 https://docs.microsoft.com/en-us/power-bi/admin/service-premium-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!


18 views0 comments