Incremental Refresh with Year-Week Column

[joshua1990]

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?

[v-yanjiang-msft]

Hi @joshua1990 ,

Is your problem solved? If so, would you mind accept the helpful reply as solution? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[mahoneypat]

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