How to Integrate Replit with Microsoft Power BI

1import os
2import requests
3from msal import ConfidentialClientApplication
4
5CLIENT_ID = os.environ["POWER_BI_CLIENT_ID"]
6CLIENT_SECRET = os.environ["POWER_BI_CLIENT_SECRET"]
7TENANT_ID = os.environ["POWER_BI_TENANT_ID"]
8WORKSPACE_ID = os.environ["POWER_BI_WORKSPACE_ID"]
9
10AUTHORITY = f"https://login.microsoftonline.com/{TENANT_ID}"
11SCOPES = ["https://analysis.windows.net/powerbi/api/.default"]
12API_BASE = "https://api.powerbi.com/v1.0/myorg"
13
14# Initialize MSAL confidential client for client credentials flow
15app = ConfidentialClientApplication(
16    CLIENT_ID,
17    authority=AUTHORITY,
18    client_credential=CLIENT_SECRET
19)
20
21
22def get_access_token() -> str:
23    """Acquire Power BI access token using client credentials."""
24    result = app.acquire_token_for_client(scopes=SCOPES)
25    if "access_token" not in result:
26        raise Exception(f"Token error: {result.get('error_description', result)}")
27    return result["access_token"]
28
29
30def get_headers() -> dict:
31    return {"Authorization": f"Bearer {get_access_token()}"}
32
33
34def list_reports(workspace_id: str = None) -> list:
35    """List all reports in a workspace."""
36    wid = workspace_id or WORKSPACE_ID
37    resp = requests.get(
38        f"{API_BASE}/groups/{wid}/reports",
39        headers=get_headers()
40    )
41    resp.raise_for_status()
42    return resp.json().get("value", [])
43
44
45def generate_embed_token(report_id: str, workspace_id: str = None) -> dict:
46    """Generate an embed token for embedding a Power BI report."""
47    wid = workspace_id or WORKSPACE_ID
48    payload = {
49        "accessLevel": "View",
50        "allowSaveAs": False
51    }
52    resp = requests.post(
53        f"{API_BASE}/groups/{wid}/reports/{report_id}/GenerateToken",
54        json=payload,
55        headers=get_headers()
56    )
57    resp.raise_for_status()
58    return resp.json()
59
60
61def push_rows_to_dataset(dataset_id: str, table_name: str, rows: list) -> dict:
62    """Push rows to a Power BI streaming dataset."""
63    resp = requests.post(
64        f"{API_BASE}/datasets/{dataset_id}/tables/{table_name}/rows",
65        json={"rows": rows},
66        headers=get_headers()
67    )
68    resp.raise_for_status()
69    return {"status": resp.status_code}
70
71
72if __name__ == "__main__":
73    reports = list_reports()
74    print(f"Reports in workspace ({len(reports)}):")
75    for r in reports:
76        print(f"  {r['id']}: {r['name']}")
77
78    if reports:
79        first_report_id = reports[0]["id"]
80        embed = generate_embed_token(first_report_id)
81        print(f"\nEmbed token for '{reports[0]['name']}'")
82        print(f"  Embed URL: {reports[0]['embedUrl']}")
83        print(f"  Token expires: {embed.get('expiration')}")

1const express = require('express');
2const axios = require('axios');
3const { ConfidentialClientApplication } = require('@azure/msal-node');
4
5const app = express();
6app.use(express.json());
7
8const CLIENT_ID = process.env.POWER_BI_CLIENT_ID;
9const CLIENT_SECRET = process.env.POWER_BI_CLIENT_SECRET;
10const TENANT_ID = process.env.POWER_BI_TENANT_ID;
11const WORKSPACE_ID = process.env.POWER_BI_WORKSPACE_ID;
12
13const AUTHORITY = `https://login.microsoftonline.com/${TENANT_ID}`;
14const SCOPES = ['https://analysis.windows.net/powerbi/api/.default'];
15const API_BASE = 'https://api.powerbi.com/v1.0/myorg';
16
17const msalApp = new ConfidentialClientApplication({
18  auth: { clientId: CLIENT_ID, authority: AUTHORITY, clientSecret: CLIENT_SECRET }
19});
20
21async function getAccessToken() {
22  const result = await msalApp.acquireTokenByClientCredential({ scopes: SCOPES });
23  if (!result?.accessToken) throw new Error('Failed to acquire Power BI token');
24  return result.accessToken;
25}
26
27async function getHeaders() {
28  const token = await getAccessToken();
29  return { Authorization: `Bearer ${token}` };
30}
31
32// List reports in workspace
33app.get('/reports', async (req, res) => {
34  const workspaceId = req.query.workspace_id || WORKSPACE_ID;
35  try {
36    const headers = await getHeaders();
37    const { data } = await axios.get(`${API_BASE}/groups/${workspaceId}/reports`, { headers });
38    res.json(data.value.map(r => ({ id: r.id, name: r.name, embedUrl: r.embedUrl })));
39  } catch (err) {
40    res.status(err.response?.status || 500).json({ error: err.message });
41  }
42});
43
44// Generate embed token for a report
45app.post('/embed-token', async (req, res) => {
46  const { report_id, workspace_id } = req.body;
47  if (!report_id) return res.status(400).json({ error: 'report_id required' });
48  const wid = workspace_id || WORKSPACE_ID;
49  try {
50    const headers = await getHeaders();
51    const { data: report } = await axios.get(
52      `${API_BASE}/groups/${wid}/reports/${report_id}`,
53      { headers }
54    );
55    const { data: tokenData } = await axios.post(
56      `${API_BASE}/groups/${wid}/reports/${report_id}/GenerateToken`,
57      { accessLevel: 'View', allowSaveAs: false },
58      { headers }
59    );
60    res.json({
61      embed_url: report.embedUrl,
62      embed_token: tokenData.token,
63      expiration: tokenData.expiration,
64      report_id
65    });
66  } catch (err) {
67    res.status(err.response?.status || 500).json({ error: err.message });
68  }
69});
70
71// Push streaming data to a dataset
72app.post('/push-data', async (req, res) => {
73  const { dataset_id, table_name, rows } = req.body;
74  if (!dataset_id || !table_name || !rows) {
75    return res.status(400).json({ error: 'dataset_id, table_name, and rows required' });
76  }
77  try {
78    const headers = await getHeaders();
79    await axios.post(
80      `${API_BASE}/datasets/${dataset_id}/tables/${table_name}/rows`,
81      { rows },
82      { headers }
83    );
84    res.json({ success: true, rows_pushed: rows.length });
85  } catch (err) {
86    res.status(err.response?.status || 500).json({ error: err.message });
87  }
88});
89
90app.listen(3000, '0.0.0.0', () => {
91  console.log('Power BI integration server running on port 3000');
92});