Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago

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?

Need Help

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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