Forum Discussion
Incremental Refresh with Year-Week Column
Hi all! I have a impala datasource with a huge table that just contains a date column for year-week 'YYYY-WW'. Is there any chance to implement an incremental refresh in here?
If your YW column had YYYYWW and was an integer, you could just follow the approach shown in this video.
Incremental Refresh with Integers in Power BI - YouTube
Since it is a text format, a different approach is needed (same basic steps as in video, but different logic). You can use a query like this, which still folds back to your database (at least it did with SQL Server). You need to create the RangeStart and RangeEnd paramaters with DateTime type first and the YYYYMM_List step generates a distinct list of the YearMonths (use same approach for YearWeeks), and then List.Contains in the filter will fold back to your source to only incrementally load the proper YearWeek rows.
let
Source = Sql.Database("localhost", "Sandbox"),
dbo_Dates =
Source{[
Schema = "dbo",
Item = "Dates"
]}
[Data],
YYYYMM_List =
List.Distinct(
List.Transform(
List.Dates(
Date.From(RangeStart),
Duration.TotalDays(
Date.From(RangeEnd)
- Date.From(RangeStart)
)
+ 1,
#duration(1, 0, 0, 0)
),
each Date.ToText(_, "yyyy-MM")
)
),
Custom1 = dbo_Dates,
#"Filtered Rows" =
Table.SelectRows(
Custom1,
each List.Contains(YYYYMM_List, [YM])
)
in
#"Filtered Rows"
Pat