Power BI is Microsoft's Business Intelligence solution - powerful, but often isolated from other systems. In this guide, we show you how to connect Power BI with automation using n8n and Make.com: automatically refresh data, distribute reports, and send alerts when KPIs change.

Why Automate Power BI?

Typical Problems:

ProblemManual EffortAutomated Solution
Data RefreshDaily manualAutomatic on change
Report DistributionEmail exportAutomatic delivery
KPI MonitoringCheck dashboardProactive alerts
Data Source SyncMaintain ETL jobsEvent-based updates

Power BI REST API Basics

Setting Up API Access

  1. Register Azure AD App:
    • Azure Portal -> App registrations -> New registration
    • Name: "Power BI Automation"
    • Redirect URI: https://n8n.your-domain.com/oauth2/callback
  2. Add Permissions:
    • API permissions -> Add permission -> Power BI Service
    • Delegated: Dataset.ReadWrite.All, Report.Read.All
  3. Create Client Secret:
    • Certificates & secrets -> New client secret
    • Copy and store secret securely

Authentication

// Get OAuth2 token
const tokenResponse = await fetch('https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token', {
  method: 'POST',
  headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
  body: new URLSearchParams({
    client_id: 'YOUR_CLIENT_ID',
    client_secret: 'YOUR_CLIENT_SECRET',
    scope: 'https://analysis.windows.net/powerbi/api/.default',
    grant_type: 'client_credentials'
  })
});

const { access_token } = await tokenResponse.json();

Workflow 1: Automatic Dataset Refresh

The Goal

Automatically refresh datasets when source data changes.

Trigger Options

Option A: Scheduled
Schedule (daily 6:00 AM)
        |
    Power BI: Dataset Refresh

Option B: Event-based
Database (new data)
        |
    Power BI: Dataset Refresh

Option C: Webhook
External System (Webhook)
        |
    Power BI: Dataset Refresh

n8n Implementation

Node 1: Trigger (Schedule or Webhook)

Node 2: Start Power BI Refresh

// Node: HTTP Request
{
  "method": "POST",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/datasets/{{ $env.DATASET_ID }}/refreshes",
  "headers": {
    "Authorization": "Bearer {{ $json.access_token }}",
    "Content-Type": "application/json"
  },
  "body": {
    "notifyOption": "MailOnFailure"
  }
}

Node 3: Check Refresh Status

// Power BI Refresh is async - query status
// Node: HTTP Request
{
  "method": "GET",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/datasets/{{ $env.DATASET_ID }}/refreshes?$top=1",
  "headers": {
    "Authorization": "Bearer {{ $json.access_token }}"
  }
}

Node 4: Wait for Completion

// Node: Wait + Loop
const refreshStatus = $json.value[0].status;

if (refreshStatus === 'Completed') {
  return { success: true };
} else if (refreshStatus === 'Failed') {
  throw new Error('Refresh failed: ' + $json.value[0].serviceExceptionJson);
} else {
  // Still in progress - check again
  await wait(30000); // 30 seconds
  // ... Loop
}

Workflow 2: Send Report via Email

The Goal

Automatically export reports as PDF and send via email.

The Workflow

Schedule (Monday 8:00 AM)
        |
    Power BI: Export report (PDF)
        |
    Wait for export
        |
    Download PDF
        |
    Send email with attachment

n8n Implementation

Step 1: Start Export

// Node: HTTP Request
{
  "method": "POST",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/reports/{{ $env.REPORT_ID }}/ExportTo",
  "headers": {
    "Authorization": "Bearer {{ $json.access_token }}",
    "Content-Type": "application/json"
  },
  "body": {
    "format": "PDF",
    "powerBIReportConfiguration": {
      "pages": [
        { "pageName": "ReportSection1" }
      ]
    }
  }
}

Step 2: Check Export Status

// Node: HTTP Request (Loop until done)
{
  "method": "GET",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/reports/{{ $env.REPORT_ID }}/exports/{{ $json.id }}",
  "headers": {
    "Authorization": "Bearer {{ $json.access_token }}"
  }
}

Step 3: Download PDF

// Node: HTTP Request (Binary)
{
  "method": "GET",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/reports/{{ $env.REPORT_ID }}/exports/{{ $json.id }}/file",
  "headers": {
    "Authorization": "Bearer {{ $json.access_token }}"
  },
  "responseFormat": "file"
}

Step 4: Send Email

// Node: Send Email
{
  "to": "management@company.com",
  "subject": "Weekly Sales Report - Week {{ $now.weekNumber }}",
  "body": "Please find the current sales report attached.",
  "attachments": [
    {
      "filename": "Sales-Report-Week{{ $now.weekNumber }}.pdf",
      "data": "{{ $binary.data }}"
    }
  ]
}

Workflow 3: KPI Alerts

The Goal

Get notified when KPIs exceed or fall below certain thresholds.

The Workflow

Schedule (hourly)
        |
    Power BI: Execute DAX query
        |
    Check KPI values
        |
    Threshold exceeded?
        |
    Yes: Send alert (Slack/Email)

Execute DAX Query

// Node: HTTP Request
{
  "method": "POST",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/datasets/{{ $env.DATASET_ID }}/executeQueries",
  "headers": {
    "Authorization": "Bearer {{ $json.access_token }}",
    "Content-Type": "application/json"
  },
  "body": {
    "queries": [
      {
        "query": "EVALUATE SUMMARIZECOLUMNS('Date'[Month], 'Sales'[Total Revenue], 'Sales'[Order Count])"
      }
    ],
    "serializerSettings": {
      "includeNulls": true
    }
  }
}

Alert Logic

// Node: Code
const results = $json.results[0].tables[0].rows;
const currentMonth = results[results.length - 1];

const alerts = [];

// Revenue Check
if (currentMonth['Sales[Total Revenue]'] < 100000) {
  alerts.push({
    type: 'warning',
    kpi: 'Revenue',
    value: currentMonth['Sales[Total Revenue]'],
    threshold: 100000,
    message: 'Revenue below target!'
  });
}

// Order Count Check
if (currentMonth['Sales[Order Count]'] < 500) {
  alerts.push({
    type: 'critical',
    kpi: 'Orders',
    value: currentMonth['Sales[Order Count]'],
    threshold: 500,
    message: 'Orders critically low!'
  });
}

return { alerts, hasAlerts: alerts.length > 0 };

Slack Alert

// Node: Slack (when hasAlerts = true)
{
  "channel": "#kpi-alerts",
  "attachments": [
    {
      "color": "{{ $json.alerts[0].type === 'critical' ? 'danger' : 'warning' }}",
      "title": "KPI Alert",
      "fields": $json.alerts.map(a => ({
        "title": a.kpi,
        "value": `${a.value} (Target: ${a.threshold})`,
        "short": true
      }))
    }
  ]
}

Workflow 4: Push Data to Power BI

Create Push Datasets

Power BI supports push datasets for real-time data:

// Create dataset
{
  "method": "POST",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/datasets",
  "body": {
    "name": "Real-Time Sales",
    "defaultMode": "Push",
    "tables": [
      {
        "name": "Sales",
        "columns": [
          { "name": "Timestamp", "dataType": "DateTime" },
          { "name": "Product", "dataType": "String" },
          { "name": "Amount", "dataType": "Double" },
          { "name": "Quantity", "dataType": "Int64" }
        ]
      }
    ]
  }
}

Push Data

// On new order: Send data to Power BI
{
  "method": "POST",
  "url": "https://api.powerbi.com/v1.0/myorg/groups/{{ $env.WORKSPACE_ID }}/datasets/{{ $env.PUSH_DATASET_ID }}/tables/Sales/rows",
  "body": {
    "rows": [
      {
        "Timestamp": "{{ $now.toISO() }}",
        "Product": "{{ $json.product_name }}",
        "Amount": {{ $json.total }},
        "Quantity": {{ $json.quantity }}
      }
    ]
  }
}

Make.com Scenario

Modules for Power BI

Make.com has native Power BI modules:

  1. Power BI -> Refresh a Dataset
  2. Power BI -> Export a Report
  3. Power BI -> Execute a Query

Example Scenario

Schedule (daily 6:00 AM)
        |
    Power BI: Refresh Dataset
        |
    Wait: 10 minutes
        |
    Power BI: Export Report to PDF
        |
    Wait: 5 minutes
        |
    Power BI: Get Export Status
        |
    Gmail: Send with Attachment

Integration with Other Data Sources

SQL Server -> Power BI

SQL Server (new data)
        |
    Trigger: New rows
        |
    Power BI: Dataset Refresh

Google Analytics -> Power BI

Schedule (daily)
        |
    Google Analytics: Get report
        |
    Transform data
        |
    Power BI: Push Dataset

Shopify -> Power BI (Real-time)

This is a powerful addition to any e-commerce automation setup.

Shopify Webhook (new order)
        |
    Format data
        |
    Power BI: Push to Real-Time Dataset

Best Practices

1. Mind the Refresh Limits

Power BI PlanRefreshes/Day
Pro8
Premium48
Premium per User48

2. Error Handling

// Catch refresh errors
try {
  await refreshDataset();
} catch (error) {
  if (error.status === 429) {
    // Rate limit - try again later
    await wait(3600000); // 1 hour
  } else if (error.status === 401) {
    // Token expired - re-authenticate
    await refreshToken();
  } else {
    await sendAlert(error);
  }
}

3. Use Incremental Refresh

Instead of full refresh, only load new data:

Power BI Desktop:
- Enable Incremental Refresh
- Define RangeStart/RangeEnd parameters
- Only delta data is loaded

4. Monitor Gateway Status

// Check gateway status
const gateways = await fetch(
  'https://api.powerbi.com/v1.0/myorg/gateways',
  { headers: { 'Authorization': `Bearer ${token}` } }
);

for (const gateway of gateways.value) {
  if (gateway.publicKey === null) {
    await sendAlert(`Gateway ${gateway.name} is offline!`);
  }
}

Costs

ComponentCost
Power BI Pro$10/User/Month
Power BI PremiumFrom $4,995/Month
n8n CloudFrom $20/Month
Make.comFrom $9/Month

Conclusion

Power BI automation enables:

  • Real-time data updates
  • Automatic report distribution
  • Proactive KPI monitoring
  • Integration with other systems

Next Steps

  1. Register Azure AD App
  2. Test Power BI API
  3. Create first refresh workflow
  4. Set up alerts
  5. Automate report distribution

I can help you with Power BI automation, from setup to production use. Book a free call. For more on connecting business systems, see our guide on API integration explained.