Power BI Integration: Automatically Refresh Data with n8n and Make.com

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:

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

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

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

We can help you with Power BI automation, from setup to production use. Contact us for a free consultation. For more on connecting business systems, see our guide on API integration explained.