• 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!


#incrementalrefresh

12 views0 comments

© 2023 by Zoe Douglas (DataZoe). Proudly created with Wix.com.

  • LinkedIn