How to Refresh a Specific Table in Power BI Using Power Automate

This tutorial walks you through setting up automated table-level refresh for a Power BI semantic model using Azure Service Principal and Power Automate. By the end, you'll have a flow that refreshes one or more specific tables on demand or on a schedule, without touching the rest of your model.

Why Table-Level Refresh?

Standard Power BI scheduled refresh processes the entire semantic model. For large models, this is wasteful when only one or two tables have updated source data. The Power BI Enhanced Refresh REST API lets you target specific tables (or even partitions). This means faster refreshes, lower capacity load, and direct control over what gets processed and when.

Prerequisites

• Power BI Premium, PPU, or Embedded capacity
• Azure subscription with permissions to create App Registrations
• Power BI workspace with a published semantic model
• Power Automate license (included with most Microsoft 365 plans)

Part 1: Azure Service Principal Setup

Step 1: Create an Azure App Registration

1. Go to portal.azure.com and sign in with an account that has permission to create App Registrations.
2. Search for App registrations and click into the service.
3. Click New registration and fill in the details:
   • Name: PowerBI-Table-Refresh-Service (or any descriptive name)
   • Supported account types: "Accounts in this organizational directory only"
   • Redirect URI: Leave blank
4. Click Register.

After registration, copy and save these values. You'll need them later:

• Application (client) ID
• Directory (tenant) ID

Step 2: Create a Client Secret

1. In your new App Registration, go to Certificates & secrets.
2. Click New client secret.
3. Set a description (e.g., PBI Refresh Secret) and an expiry (12-24 months recommended).
4. Click Add.
5. Immediately copy the secret Value (not the Secret ID). You won't be able to retrieve it later. Store it securely.

Step 3: A Note on API Permissions

When using a service principal with the client_credentials OAuth flow, the Delegated permissions configured in the Azure portal do not take effect. The service principal's access is controlled by its workspace role in Power BI (configured in Part 2 below).

This trips people up regularly. You do not need to add Power BI Service delegated permissions or grant admin consent for this scenario. The workspace role is what matters.

Part 2: Power BI Service Configuration

Step 4: Enable Service Principal Access in Power BI

1. Go to app.powerbi.com and sign in as a Power BI Administrator.
2. Click the gear icon, then Admin portal.
3. Navigate to Tenant settings.
4. Scroll to the Developer settings section.
5. Find the setting Service principals can use Fabric APIs (previously named "Allow service principals to use Power BI APIs").
6. Toggle it to Enabled.
7. Under "Apply to", select Specific security groups and add a security group that contains your service principal. This is the recommended approach. Avoid enabling it for the entire organization.
8. Click Apply.

Step 5: Add the Service Principal to Your Workspace

1. In Power BI Service, navigate to the workspace containing the semantic model you want to refresh.
2. Click Access (the people icon) in the workspace header.
3. Click Add people or groups.
4. Search for your service principal name (e.g., PowerBI-Table-Refresh-Service).
5. Assign the role Member or Admin.
6. Click Add.

The service principal now has the permissions it needs to trigger refreshes on semantic models in this workspace.

Part 3: Power Automate Flow Setup

Step 6: Create the Flow

1. Go to make.powerautomate.com (the current URL, flow.microsoft.com redirects here).
2. Click Create > Instant cloud flow.
3. Name it Power BI Table Refresh and select Manually trigger a flow as the trigger.
4. Click Create.

Step 7: Get an Access Token

The first action in your flow authenticates the service principal and retrieves a Bearer token.

Add an HTTP action with the following configuration:

Method: POST

URI: https://login.microsoftonline.com/{tenant-id}/oauth2/v2.0/token

Headers:
Content-Type: application/x-www-form-urlencoded

Body:
grant_type=client_credentials&client_id={client-id}&client_secret={client-secret}&scope=https://analysis.windows.net/powerbi/api/.default

Replace {tenant-id}, {client-id}, and {client-secret} with the values from Part 1.

Step 8: Parse the Token Response

Add a Parse JSON action after the token request:

• Content: Select body from the previous HTTP action.
• Schema:

{
  "type": "object",
  "properties": {
    "access_token": {
      "type": "string"
    },
    "token_type": {
      "type": "string"
    },
    "expires_in": {
      "type": "integer"
    }
  }
}

Step 9: Trigger the Table-Level Refresh

Add a second HTTP action to call the Enhanced Refresh API:

Method: POST

URI: https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/datasets/{dataset-id}/refreshes

Headers:
Content-Type: application/json
Authorization: Bearer @{body('Parse_JSON')?['access_token']}

Request body - this is where you specify which table(s) to refresh:

{
  "type": "Full",
  "commitMode": "transactional",
  "maxParallelism": 2,
  "retryCount": 2,
  "timeout": "02:00:00",
  "objects": [
    {
      "table": "FactSales"
    }
  ]
}

The important part is the objects array. Each entry specifies a table name to refresh. You can include multiple tables:

"objects": [
  {
    "table": "FactSales"
  },
  {
    "table": "DimCustomer"
  }
]

You can also target individual partitions within a table:

"objects": [
  {
    "table": "FactSales",
    "partition": "FactSales-2024"
  }
]

Replace the placeholders:

• {workspace-id} - your workspace GUID (find it in the URL when viewing the workspace in Power BI Service)
• {dataset-id} - your semantic model GUID (find it in the URL when viewing dataset settings, or via the Power BI REST API)

Enhanced Refresh Parameters Reference

Step 10: Add Error Handling

A successful refresh request returns HTTP 202 Accepted (not 200). The refresh runs asynchronously.

1. Add a Condition action after the refresh HTTP request.
2. Set the condition: outputs('HTTP_Refresh')['statusCode'] is equal to 202.
3. In the Yes branch: optionally add a notification (Teams message, email) confirming the refresh was triggered.
4. In the No branch: capture the error. The response body will contain error details.

To track progress after the 202 response, you can poll the refresh status with a GET request:

GET https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/datasets/{dataset-id}/refreshes/{requestId}

The requestId comes from the Location header of the 202 response. Status will be InProgress, Completed, or Failed.

Part 4: Testing and Scheduling

Step 11: Test Your Flow

1. Click Test in the flow editor.
2. Select Manually > Test > Run flow.
3. Check each action for success. The token request should return 200, and the refresh request should return 202.
4. Verify in Power BI Service: go to your semantic model > Refresh history. You should see a refresh of type ViaEnhancedApi.

Step 12: Schedule the Flow (Optional)

1. Edit your flow and delete the manual trigger.
2. Add a Recurrence trigger.
3. Set your desired schedule (e.g., daily at 6:00 AM).
4. Save and make sure the flow is turned On.

Troubleshooting

HTTP 400 Bad Request on refresh call

• Another refresh may already be running. The API only allows one refresh operation at a time per semantic model.
• Verify your table names match exactly (case-sensitive).

HTTP 401 Unauthorized

• Client secret may have expired. Generate a new one in Azure.
• Service principal may not be a member of the workspace.

HTTP 403 Forbidden

• The tenant setting "Service principals can use Fabric APIs" may not be enabled for your security group.
• The service principal doesn't have a sufficient workspace role (needs Member or Admin).

HTTP 404 Not Found

• Double-check workspace ID and dataset ID GUIDs.

Refresh shows as Failed in Power BI

• Use GET /refreshes/{requestId} to get detailed per-object status and error messages.
• Check data source credentials. Service principals may need gateway or data source configuration depending on your setup.

Security Best Practices

• Use Azure Key Vault to store the client secret. Never hardcode secrets in Power Automate flows.
• Rotate secrets regularly. Set a calendar reminder before expiry.
• Use a dedicated security group for service principal access. Don't enable the tenant setting for the entire organization.
• Principle of least privilege. Assign the Member role (not Admin) unless Admin is specifically needed.
• Monitor refresh activity. Enable flow run history and check Power BI refresh logs periodically.

Next Steps

Once table-level refresh is working, here are some things worth trying:

• Partition-level refresh for tables with incremental refresh policies. Refresh only the latest partition instead of the full table.
• Chained refreshes. Refresh dimension tables first, then fact tables, using sequential HTTP actions.
• Status polling loop. Add a Do Until loop that polls GET /refreshes/{requestId} until status is Completed or Failed.
• Multi-model support. Extend the flow to refresh tables across multiple semantic models.
• Failure alerts. Send Teams or email notifications when a refresh fails.