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
Go to New Source --> Blank Query
Name it ConvertDateTimeToDateID
Go to Advanced Editor
Paste in the following: let Source = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x) in Source
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!
Comments