Incremental Refresh by Year and not by date

[bkan]

Is it possible to do incremental refres by YEAR rather than start and end dates?

Thanks

[lbendlin]

Partitions can be by year, quarter, month, or day.

So yes, you can specify yearly partitions.  You will still need to provide a datetime column for the RangeStart and RangeEnd parameters  (for example the first day of the year)

[bkan]

@lbendlin  thanks. The issue here is in the backend we have a VIEW and are converting date into YEAR and MONTH, so if we bring in the date we  are runnig into millions of records per year, for example we average 12 to 15 mil rows if we add the date field.

Thanks

[lbendlin]

You don't need to materialize the field.

RangeStart <= #datetime([year],1,1,0,0,0) and #datetime([year],1,1,0,0,0) < RangeEnd

[bkan]

@lbendlin so my understanding is create 2 params Rangestart and RangeEnd and do it in PBI Desktop?  What does  #datetime([year],1,1,0,0,0)  do is that a PBI Function?

Thanks

[lbendlin]

that is a PowerQuery M function that creates a datetime value from your [year] column. First day of the year, midnight.

[bkan]

@lbendlin  thanks . I created the Paramete "RangeStart" and "RangeEnd". Please tell me where to write the M function. My advanced editor shows the code as

let
Source = Sql.Databases("DEV"),
QADataWareHouse = Source{[Name="QADataWareHouse"]}[Data],
dbo_Report_OverView = QADataWareHouse{[Schema="dbo",Item="Report_OverView"]}[Data],
#"Renamed Columns" = Table.RenameColumns(dbo_Report_OverView,{{"LOB", "Services"}, {"ClientName", "Client Name"}})
in
#"Renamed Columns"

[lbendlin]

The filter needs to be part of the query for the data you want to load into Power BI incrementally. Add the filter as soon as possible in the code.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

[bkan]

@lbendlin Thanks I tried the below code in the Advanced Editor and it is still refreshing all the YEARS and not just the current year

[lbendlin · Accepted solution]

You created a Power Query Step called "where"  and it will produce a logical true/false but otherwise be completely disjointed from your table.

let
Source = Sql.Databases("server\instance"),
DataWareHouse = Source{[Name="Database"]}[Data],
dbo_OverView = DataWareHouse{[Schema="dbo",Item="_OverView"]}[Data],
Filtered = Table.SelectRows(dbo_OverView, each RangeStart <= #datetime([WorkDate_Year],1,1,0,0,0) and #datetime([WorkDate_Year],1,1,0,0,0) < RangeEnd),
#"Renamed Columns" = Table.RenameColumns(Filtered,{{"LOB", "Services"}, {"ClientName", "Client Name"}})
in
#"Renamed Columns"

[bkan]

thanks can you please delete the solution as by mistake i have some sensitive info

[lbendlin]

I cleaned it up.

[bkan]

will implement and update you