How to Integrate Retool with Microsoft Power BI

Power BI's REST API requires Azure Active Directory authentication. You must register an app in your Azure AD tenant to get the OAuth credentials Retool will use. Navigate to portal.azure.com and sign in with an account that has permission to register Azure AD applications (Azure AD → App Registrations requires at minimum Application Developer or Global Administrator role). Go to Azure Active Directory → App Registrations → New Registration. Fill in the registration form: - Name: 'Retool Power BI Integration' or similar - Supported account types: 'Accounts in this organizational directory only' (for single-tenant/internal use) - Redirect URI: Select 'Web' and enter your Retool OAuth callback URL. For Retool Cloud, this is https://oauth.retool.com/oauth/user/oauthcallback. For self-hosted Retool, it is https://your-retool-instance.com/oauth/user/oauthcallback. Click Register. On the app's overview page, note the Application (client) ID and Directory (tenant) ID — you'll need both for the Retool resource configuration. Navigate to Certificates & Secrets → Client Secrets → New Client Secret. Add a description and expiry (24 months is common for internal tooling). Copy the Value immediately — it is only shown once. Navigate to API Permissions → Add a Permission → Power BI Service. Select Delegated Permissions and add: Report.Read.All, Dataset.Read.All, Dataset.ReadWrite.All (if you need refresh triggers), Workspace.Read.All. Click Grant Admin Consent to pre-approve these permissions for all users in your tenant — this prevents individual consent prompts for each Retool user.

With your Azure AD app credentials ready, configure the Retool REST API Resource to authenticate with Power BI's API. Open Retool and navigate to the Resources tab. Click Add Resource and select REST API. Fill in the configuration: - Name: 'Microsoft Power BI' - Base URL: https://api.powerbi.com/v1.0/myorg For authentication, select OAuth 2.0 from the Auth dropdown. Configure the OAuth 2.0 settings: - Grant type: Authorization Code - Authorization URL: https://login.microsoftonline.com/{{ YOUR_TENANT_ID }}/oauth2/v2.0/authorize (replace with your actual tenant ID) - Access Token URL: https://login.microsoftonline.com/{{ YOUR_TENANT_ID }}/oauth2/v2.0/token - Client ID: paste your Azure AD app's Application (client) ID - Client Secret: paste the client secret value you saved - Scope: https://analysis.windows.net/powerbi/api/.default offline_access - Share OAuth 2.0 credentials between users: enable this if all Retool users should access Power BI under a single service account identity (common for internal tools) Click Connect Account. A Microsoft login popup opens — sign in with the Power BI account that has access to the workspaces you need. After authentication, the resource shows a green Connected status. Add a default header to all requests: Content-Type: application/json. Click Save Changes.

With the resource configured, you can now build queries that interact with the Power BI REST API. Create a new query in your Retool app and select the Power BI resource. To list all workspaces (groups) you have access to: - Method: GET - Path: /groups Run this query to verify the connection. The response contains an array of workspace objects, each with an id, name, and isOnDedicatedCapacity flag. To list reports in a specific workspace: - Method: GET - Path: /groups/{{ workspaceSelect.value }}/reports Bind the path parameter to a Select component populated with workspace data from the first query. Use a transformer on the first query to map workspaces to { label: ws.name, value: ws.id } format for the Select component. To list datasets: - Method: GET - Path: /groups/{{ workspaceSelect.value }}/datasets To get refresh history for a specific dataset: - Method: GET - Path: /groups/{{ workspaceSelect.value }}/datasets/{{ datasetTable.selectedRow.id }}/refreshes Set up all queries to run automatically when their inputs change (workspace selection, dataset selection) using the 'Run this query automatically when inputs change' toggle. Add 'Only run when' conditions to prevent errors when no workspace or dataset is selected.

1// JavaScript transformer to format workspace list for Select component
2// Apply on the List Workspaces query
3const workspaces = data.value || [];
4return workspaces.map(ws => ({
5  label: ws.name,
6  value: ws.id,
7  is_premium: ws.isOnDedicatedCapacity,
8  type: ws.type
9}));
10
11// JavaScript transformer for datasets table
12// Shows refresh status and timing at a glance
13const datasets = data.value || [];
14return datasets.map(ds => ({
15  id: ds.id,
16  name: ds.name,
17  configured_by: ds.configuredBy || '',
18  is_refreshable: ds.isRefreshable,
19  is_on_prem_gateway: ds.isOnPremGatewayRequired,
20  last_refresh: ds.Refresh?.endTime
21    ? new Date(ds.Refresh.endTime).toLocaleString()
22    : 'Never refreshed'
23}));

Power BI scheduled refreshes run at configured intervals, but operations teams often need to trigger an immediate refresh when they know source data has updated. Build a dataset refresh trigger directly in Retool. Create a new query with: - Method: POST - Path: /groups/{{ workspaceSelect.value }}/datasets/{{ datasetTable.selectedRow.id }}/refreshes - Body type: JSON - Body: {} (Power BI accepts an empty JSON body for a basic on-demand refresh; you can add notifyOption parameters for email notifications) Set this query to Manual trigger. Wire it to a 'Trigger Refresh' button in the dataset table's row actions, with a confirmation dialog: 'Trigger an on-demand refresh for dataset {{ datasetTable.selectedRow.name }}?' On Success, add event handlers to: show a success notification ('Refresh triggered. May take a few minutes to complete.'), then re-run the refresh history query to see the new pending entry appear. Note: Power BI's refresh API returns 202 Accepted immediately — the actual refresh runs asynchronously. The refresh history query shows status as 'Unknown' or 'InProgress' initially, then updates to 'Completed' or 'Failed'. Consider adding a manual 'Check refresh status' button rather than auto-polling, to avoid excessive API calls. For tracking refreshes across your entire workspace, create a transformer that joins the dataset list with their latest refresh history and shows last refresh time and status in the main datasets table — giving BI administrators a full health view at a glance.

1// POST body for triggering a Power BI dataset refresh
2// Power BI also supports notifyOption for email notifications on completion
3{
4  "notifyOption": "NoNotification"
5}
6
7// JavaScript transformer to show refresh status with color coding
8// Apply on the refresh history query for the selected dataset
9const refreshes = data.value || [];
10return refreshes.slice(0, 20).map(r => ({ // show last 20 refreshes
11  id: r.requestId,
12  status: r.status,
13  refresh_type: r.refreshType,
14  start_time: r.startTime ? new Date(r.startTime).toLocaleString() : '',
15  end_time: r.endTime ? new Date(r.endTime).toLocaleString() : 'In progress',
16  duration_seconds: (r.startTime && r.endTime)
17    ? Math.round((new Date(r.endTime) - new Date(r.startTime)) / 1000)
18    : null,
19  service_exception: r.serviceExceptionJson
20    ? JSON.parse(r.serviceExceptionJson)?.errorCode || 'Error'
21    : ''
22}));

For teams that want to display actual Power BI report visuals (not just data) within Retool, use Power BI Embedded via Retool's Custom Component feature. This approach generates a short-lived embed token server-side and renders the interactive report in an iframe. First, create a query to generate an embed token: - Method: POST - Path: /groups/{{ workspaceSelect.value }}/reports/{{ reportTable.selectedRow.id }}/GenerateToken - Body type: JSON - Body: { "accessLevel": "View" } This returns an embed token (data.token) and an embed URL (data.embedUrl) valid for approximately 60 minutes. Next, create a Custom Component in Retool (Component panel → Custom Component). In the Custom Component code editor, write HTML that loads the Power BI JavaScript SDK and renders the report using the embed token from your query: Wire the Custom Component's model data to pass the embed token and URL from your Retool query: set the model to { token: {{ generateEmbedTokenQuery.data.token }}, embedUrl: {{ reportTable.selectedRow.embedUrl }} }. The component's JavaScript reads these values from the model and calls the Power BI SDK's embed function. For complex integrations combining Power BI embedding with multiple data sources, custom page-level filters, and report interaction events feeding back into Retool, RapidDev's team can help design the full architecture including token refresh logic and bidirectional data flow between the embedded report and Retool components.

1<!-- Power BI Custom Component HTML/JS -->
2<!-- Paste this into Retool's Custom Component code editor -->
3<div id="reportContainer" style="height: 600px; width: 100%;"></div>
4<script src="https://cdn.jsdelivr.net/npm/powerbi-client@2.22.3/dist/powerbi.min.js"></script>
5<script>
6  const powerbi = window['powerbi-client'].default;
7  
8  // Watch for model changes (token/URL passed from Retool queries)
9  Retool.subscribe(function(model) {
10    if (!model.token || !model.embedUrl) return;
11    
12    const config = {
13      type: 'report',
14      id: model.reportId || undefined,
15      embedUrl: model.embedUrl,
16      accessToken: model.token,
17      tokenType: powerbi.models.TokenType.Embed,
18      settings: {
19        panes: {
20          filters: { visible: true },
21          pageNavigation: { visible: true }
22        },
23        background: powerbi.models.BackgroundType.Transparent
24      }
25    };
26    
27    const container = document.getElementById('reportContainer');
28    const report = powerbi.embed(container, config);
29    
30    // Notify Retool when report loads
31    report.on('loaded', function() {
32      Retool.modelUpdate({ reportLoaded: true });
33    });
34  });
35</script>