Accessing Power BI Tables Using SPFx or Node.js

The Power BI REST API is a powerful tool for developers looking to integrate Power BI data into their applications. Whether you are building a SharePoint Framework (SPFx) web part or a standalone Node.js application, this guide provides a comprehensive walkthrough for fetching table data from Power BI.

Introduction

Power BI REST API allows you to programmatically access and manipulate workspaces, datasets, and tables. In this guide, we’ll cover two common scenarios:

Fetching Power BI table data using SPFx.
Fetching Power BI table data using Node.js.

Prerequisites

Before proceeding, ensure you have:

Azure AD App Registration:

Register an app in Azure Active Directory (AAD).
Note the clientId, tenantId, and clientSecret.
Assign the app permissions to Power BI, e.g., Dataset.Read.All.

API URL Components:

Group ID (workspace): workspaceId.
Dataset ID: datasetId.
Table name: tableName.

Environment Setup:

SPFx project or a Node.js development environment.

Scenario 1: Using SPFx to Access Power BI Tables

SPFx enables seamless integration of Power BI data within SharePoint Online.

Step 1: Configure SPFx Project for Power BI

Update Permissions
Modify package-solution.json to request Power BI API access:

   {
     "webApiPermissionRequests": [
       {
         "resource": "Microsoft Power BI",
         "scope": "Dataset.Read.All"
       }
     ]
   }

Install Required Dependencies
Install necessary packages:

   npm install @microsoft/sp-http

Step 2: Access Power BI Data in SPFx

In your SPFx project, add the following logic:

Code: PowerBIData.ts

import { AadHttpClient } from '@microsoft/sp-http';

export default class PowerBIData {
  private client: AadHttpClient;

  constructor(context: any) {
    context.aadHttpClientFactory
      .getClient("https://analysis.windows.net/powerbi/api")
      .then((client: AadHttpClient) => {
        this.client = client;
      });
  }

  public async getTableData(workspaceId: string, datasetId: string, tableName: string): Promise<any> {
    const url = `https://api.powerbi.com/v1.0/myorg/groups/${workspaceId}/datasets/${datasetId}/tables/${tableName}/rows`;

    try {
      const response = await this.client.get(url, AadHttpClient.configurations.v1);
      return await response.json();
    } catch (error) {
      console.error("Error fetching Power BI data:", error);
    }
  }
}

Step 3: Display Data in Your Web Part

Use the PowerBIData class to fetch and display the table rows in your SPFx web part.

import PowerBIData from './PowerBIData';

export default class YourWebPart {
  public render(): void {
    const powerBIData = new PowerBIData(this.context);
    powerBIData.getTableData('workspaceId', 'datasetId', 'tableName')
      .then(data => console.log(data))
      .catch(err => console.error(err));
  }
}

Scenario 2: Using Node.js to Access Power BI Tables

Node.js provides flexibility for integrating Power BI data into standalone applications or backend services.

Step 1: Setup Node.js Project

Install Required Packages

   npm install axios msal-node

Authenticate with Azure AD
Use MSAL (Microsoft Authentication Library) for Node.js to obtain an access token.

Step 2: Fetch Power BI Table Data

Code: powerbi-fetch.js

const axios = require('axios');
const msal = require('@azure/msal-node');

// Azure AD App Configuration
const config = {
  auth: {
    clientId: "YOUR_CLIENT_ID",
    authority: "https://login.microsoftonline.com/YOUR_TENANT_ID",
    clientSecret: "YOUR_CLIENT_SECRET",
  }
};

// Function to Get Access Token
const getAccessToken = async () => {
  const cca = new msal.ConfidentialClientApplication(config);
  const authResult = await cca.acquireTokenByClientCredential({
    scopes: ["https://analysis.windows.net/powerbi/api/.default"],
  });
  return authResult.accessToken;
};

// Function to Fetch Table Data
const getTableData = async (workspaceId, datasetId, tableName) => {
  const token = await getAccessToken();
  const url = `https://api.powerbi.com/v1.0/myorg/groups/${workspaceId}/datasets/${datasetId}/tables/${tableName}/rows`;

  try {
    const response = await axios.get(url, {
      headers: {
        Authorization: `Bearer ${token}`,
      },
    });
    return response.data;
  } catch (error) {
    console.error("Error fetching Power BI table data:", error);
  }
};

// Example Usage
const workspaceId = "YOUR_WORKSPACE_ID";
const datasetId = "YOUR_DATASET_ID";
const tableName = "YOUR_TABLE_NAME";

getTableData(workspaceId, datasetId, tableName)
  .then(data => console.log(data))
  .catch(err => console.error(err));

Summary Table

Step	SPFx	Node.js
Setup	Modify `package-solution.json`	Install `axios`, `msal-node`
Authentication	`AadHttpClient`	`msal-node`
Fetch Data	`AadHttpClient.get`	`axios.get` with token
Permissions	Power BI API: `Dataset.Read.All`	Power BI API: `Dataset.Read.All`

Conclusion

Integrating Power BI data into SPFx or Node.js applications is straightforward with proper authentication and API calls. SPFx is ideal for SharePoint Online, while Node.js suits standalone or backend applications.