Detailed Instructions for Managing a Power BI Push Dataset via REST API

These instructions assume a service principal authentication setup using client credentials flow, which is reliable for automation but requires careful permission management to avoid access issues. If something doesn't work as described, it could stem from tenant policies, license limits, or subtle API behaviours.

This guide focuses on the "ProductUpdates" push dataset in the "Shopify" workspace. This is a push dataset (API flags = "addRowsAPIEnabled": true and the absence of refresh options in the UI/API), meaning data is added programmatically (e.g., via Shopify N8N integration) rather than pulled from a source. Therefore, there's no need for refresh commands. Push datasets don't support standard refreshes, like used to import modes; instead, data management is via direct API interactions.

Another attention point: For empty-body requests (like DELETE for rows), some servers enforce HTTP specs strictly, leading to a 411 "Length Required" error. Always include -H "Content-Length: 0" in curl to prevent this—it's a simple but critical fix based on common troubleshooting.

1. Create the Bearer Token (Access Token)

To authenticate API calls, use a service principal (SP) with client credentials flow. This is secure for scripts but requires Azure AD setup. If your organization has restrictions (common in EU tenants), consult your admin first.

Steps:

• Register an App in Azure AD: Log into the Azure Portal (portal.azure.com) with an admin account.
  • Go to Azure Active Directory > App registrations > New registration.
  • Name it (e.g., "PowerBI-API-App"), select "Accounts in this organizational directory only" (single tenant for security), and leave Redirect URI blank (not needed for client credentials).
  • Register and note the Application (client) ID and Directory (tenant) ID from the Overview page.
• Create a Client Secret: In the app > Certificates & secrets > New client secret.
  • Add a description, set expiry (e.g., 1 year), and copy the Value (secret)—store it securely, as it won't show again.
• Grant API Permissions: In the app > API permissions > Add a permission > Power BI Service.
  • Select Application permissions: Dataset.Read.All and Dataset.ReadWrite.All (for read/delete access).
  • Grant admin consent (button at bottom)—this is crucial; without it, auth fails.
• Enable SP in Power BI Tenant Settings: In Power BI service (app.powerbi.com) > Admin portal > Tenant settings > Developer settings.
  • Enable "Allow service principals to use Power BI APIs" and apply to your organization or a security group (create one in Azure AD, add your SP's object ID).
  • Wait 15-30 minutes for propagation.
• Acquire the Token via Curl:
  text

  
  

  curl -X POST -H "Content-Type: application/x-www-form-urlencoded" \
   -d "grant_type=client_credentials&client_id={your_client_id}&client_secret={your_client_secret}&scope=https://analysis.windows.net/powerbi/api/.default" \
   "https://login.microsoftonline.com/{your_tenant_id}/oauth2/v2.0/token"

  
  

  
  
  • Replace placeholders with your values.
  • Parse the JSON response for "access_token" (a long JWT string). Tokens expire in ~1 hour—regenerate as needed.
  • Attention: If you get errors like "invalid_grant," double-check consent and scopes. In NL/EU, multi-factor auth on the admin account can interfere—use a conditional access policy if needed.
  • Only copy the token value, not the full JSON.

Testing the Token: Run a simple GET (e.g., to list workspaces below) with -H "Authorization: Bearer {access_token}". A 200 OK means it's valid; 401 indicates issues.

2. Test the Initial Connection

Before reading or deleting, verify connectivity to the workspace and dataset. This ensures permissions and IDs are correct— a pragmatic step to catch setup flaws early. Use curl with --verbose for HTTP details.

Steps:

• Get Workspace ID:
  text

  
  

  curl -X GET "https://api.powerbi.com/v1.0/myorg/groups" \
   -H "Authorization: Bearer {access_token}" \
   --verbose

  
  

  
  
  • Search the JSON "value" array for "name": "Shopify" and note its "id" (e.g., "ABDCEDFG-1234-1234-1234-ABCDEFGABCD").
  • If empty, the SP lacks workspace access—add it via UI (Workspace > Access > Add SP by object ID).
• Get Dataset ID:
  text

  
  

  curl -X GET "https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets" \
   -H "Authorization: Bearer {access_token}" \
   --verbose

  
  

  
  
  • Search for "name": "ProductUpdates" and note its "id" (e.g., "12345678-ABCD-ABCD-ABCD-12345678901").
  • If not found, confirm in UI—push datasets can be hidden if unpublished.
• Get Dataset Details (Final Connectivity Check):
  text

  
  

  curl -X GET "https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}" \
   -H "Authorization: Bearer {access_token}" \
   --verbose

  
  

  
  
  • A 200 OK with JSON (e.g., confirming "name": "ProductUpdates") means full access. Flags like "addRowsAPIEnabled": true confirm it's push-based—no refresh needed.

Attention Points/Lessons Learned: If responses are empty despite valid tokens, it's often SP permissions (e.g., not added to the workspace). We learned this the hard way—initial empty lists led to enabling tenant settings. In EU setups, data residency might route to specific endpoints (e.g., wabi-west-europe-e); if redirects fail, check network/firewall.

3. Read the Data

For push datasets, view data via UI (easiest) or API (for scripting). If empty, push test rows first (not covered here, as it's additive).

UI Method (Recommended for Quick Inspection):

• In Power BI service > Workspaces > Shopify > Semantic models tab.
• Ellipsis (...) on "ProductUpdates" > Create report.
• In editor: Add a Table visual, drag fields from right pane to Values.
• Data appears if present; export via ellipsis > Export data (CSV/Excel).
• Lesson: Fields only show if tables exist— if blank, dataset might be structure-only.

API Method (Using DAX Query):

• First, list tables:
  text

  
  

  curl -X GET "https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/tables" \
   -H "Authorization: Bearer {access_token}" \
   --verbose

  
  

  
  
  • Note table names from "value" array.
• Then, query a table (replace {tableName}; use EVALUATE for all rows):
  text

  
  

  curl -X POST "https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/executeQueries" \
   -H "Authorization: Bearer {access_token}" \
   -H "Content-Type: application/json" \
   -H "Content-Length: 0"  # Include to avoid 411 if body is empty, though here it's not
   -d '{"queries": [{"query": "EVALUATE \u0027{tableName}\u0027"}]}' \
   --verbose

  
  

  
  
  • {tableName} could be named "RealTimeData" for push datasets.
  • Data in JSON "results" > "tables" > "rows". For large sets, add TOPN(100, 'tableName') to page.
  • Attention: DAX errors (400) mean invalid syntax—escape quotes carefully. Limits: ~10,000 rows/query; throttle on shared capacity.

Attention Points: If no data shows, it's likely empty—push datasets start blank. We confirmed data visibility this way, but be open: Query performance varies; use UI for complex exploration.

4. Delete the Data

This clears rows from tables—irreversible, so test on non-critical data. Repeat for each table.

Steps:

• List tables (as in Read section above) to get names.
• Delete rows from a table:
  text

  
  

  curl -X DELETE "https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/tables/{tableName}/rows" \
   -H "Authorization: Bearer {access_token}" \
   -H "Content-Length: 0" \
   --verbose

  
  

  
  
  • {tableName} could be named "RealTimeData" for push datasets.
  • 200 OK means success. Repeat for all tables.
• Verify: Re-query data—should be empty.

Attention Points/Lessons Learned: DELETE requires ReadWrite permissions; 403 errors mean re-grant in Azure AD. The 411 error was a key lesson—always add Content-Length for empty requests. For push datasets, this is the only way to clear data (no refresh to "reset"). If deleting the whole dataset, use DELETE on /datasets/{datasetId}, but that's destructive.