How to refresh a table Incrementally as well as Full Refresh

[MKV]

Hi All,

I have HISTORY and CURRENT tables in the Semantic Model.

All the HISTORY tables have years of data and no of rows/columns are big there.

I want to setup below refresh policy on all the HISTORY tables to improve the performance -

1) On Sunday, the Full refresh on HISTORY tables should run to make sure that if there is a data change or some of the historical data is deleted the full refresh table would bring the updated data.
2) On any other day(Monday to Friday), the incremental refresh should run and it should refresh the previous 2 days + current day data incrementally everyday.

Can someone please help how can I achieve it?

I do know how to setup the incremental refresh. I dont have access on Sql Sever Mgmt Studio.

Regards,
MKV

[MKV]

@lbendlin Thanks again. I just noticed that FULL refresh is taking the Old Range Start and Range End date that I had selected when I setup the incremental refresh. so database query is like "SELECT COLUMNS FROM TABLE WHERE BUSINESS_DATE >= RANGE START AND BUSINESS_DATE < RANGE END. How to change the Rane End date so that it should refresh till as of date data?

[lbendlin]

That should not happen unless you modify the partition rules.  Run another automatic refresh and see if the partitions are being recreated/refilled

[lbendlin · Accepted solution]

Keep the incremental refresh as is, and then in Power Automate create a custom connector that allows you to initiate arbitrary refresh requests (including full refreshes).  Then you can run a weekly flow to ask for a full refresh.

Enhanced refresh with the Power BI REST API - Power BI | Microsoft Learn

[MKV]

Thanks @lbendlin . It is really helpful. I started the refresh using this method through AZURE DATA FACTORY but I am not able to verify if FULL refresh is running on table or incremental refresh. When I am checking the running queries in the database, I found many queries are running with date conditions. How can I verify if FULL refresh is running?

I passed below syntax in the body of Rest API - 

{

    "type": "Full",

    "commitMode": "transactional",

    "maxParallelism": 2,

    "retryCount": 2,

    "objects": [

        {

            "table": "LOAN_HISTORY"

        },

        {

            "table": "CUSTOMER_HISTORY"

        }

    ]

}

[lbendlin]

As long as all partitions end up having a recent refresh date you're good. If you want to you can issue a clearValues refresh too, but that's not really required.