Advanced incremental refresh via API - All 'OTHER' tables

[ajohnso2]

So I have an API to refresh a single table which has incremental refresh enabled.

Do I have to perfrom 2 refreshes of the model to ensure 'other' tables in the model are also refreshed?? I.e Standard refresh first followed by my api script to update the effective date on a table?

If possible I would like to run 1 refresh that will refresh the entire model and then ensure the effective date is correctly applied to the table with incremental enabled.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Parameters
$tenantId = "************************************"
$appId = "************************************"
$appSecret = "************************************"
$groupId = "************************************" # The workspace
$datasetId = "************************************" # The dataset

$tokenEndpoint = "https://login.microsoftonline.com/$tenantId/oauth2/token"

# Intialise variables to call authentication end point
$Authentication = 
@{
    client_id     = $appId
    client_secret = $appSecret
    grant_type    = "client_credentials"
    resource      = "https://analysis.windows.net/powerbi/api"
}

# Settings for Incremental refresh (Forms advanced API)
# Advance 1 month to ensure captured next 3 weeks forecast
$targetdate = (get-date).AddMonths(1).ToString('MM-dd-yyyy')
$body =
@"
{
	`"type`": `"full`",
	`"applyRefreshPolicy`": true,
	`"effectiveDate`": `"$targetdate`",
	`"objects`": [
{
`"database`": `"{MY MODEL}`",
        `"table`": `"{MY TABLE}`"
      }
    ]
}
"@

# Authenticate with service principal and acquire access token
$response = Invoke-RestMethod -Uri $tokenEndpoint -Method Post -Body $Authentication
$accessToken = $response.access_token

# Begin refresh operations
if ($accessToken) 
    {
      # Refresh dataset
      $headers = @{
            'Authorization' = "Bearer $accessToken"
            'Content-Type'  = 'application/json'
    }

        $refreshUrl = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes"

        # If dataset is in a specific workspace
        if ($groupId -ne $null) 
            {
                $refreshUrl = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes"
            }

    # Perform dataset refresh
    try 
    {
        $response = Invoke-RestMethod -Uri $refreshUrl -Method Post -Headers $headers -Body $body -Verbose
        Write-Host "Dataset refresh started."
    } catch 
            {
                Write-Host "Failed to start dataset refresh: $($_.Exception.Message)"
            }
    } else 
        {
            Write-Host "Failed to acquire access token."
        }

[v-yifanw-msft]

Hi @ajohnso2 ,

Based on the information you provided, when a refresh is triggered via the API to apply an incremental refresh policy and effective date, the action will target the table specified in the body of the request. The refresh behaviour for the rest of the model depends on how the dataset and its tables are configured in Power BI. To achieve your goal consider the following:

1. your current script is the correct way to apply an effective date to an incremental refresh. For tables that do not have an incremental refresh strategy, a full refresh operation will reload the entire table's data from the source.

2. ensure that your dataset is configured correctly in Power BI Desktop before publishing. Tables that require frequent updates should have an incremental refresh policy defined, while static or slow-changing tables do not require such a policy.

3. After adjusting the refresh script or dataset configuration, test the refresh operation to ensure that it behaves as expected.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.