Intro

Many times a client wants some sort of customized view of the data they have in PowerBI. The truth is, as powerful as it is PowerBI, like all BI tools, offer limited customizability. However, PowerBI comes with a powerful RESTful interface that we can use to show the underlying data however we wish. This has the benefit of using PowerBI’s caching, aggregation engine, and many other unique enhancements and insights to the data in our own custom interface. This can be anything from a webapp to a mobile app to specific hardware. In this quick post I’ll show you the basics of using a PowerBI dataset in a custom webapp via REST.

Requirements

Access to an Azure subscription (you can get one for free)
Access to PowerBI
A Github account (it’s free)
Visual Studio Code
NodeJS

Setup

React Application

We will be cloning a repository, you do not have to manually create the react app at this point.

VS code Extensions

Within VS code install both the Github extension and the Azure Static Web Apps extension. Once both are installed, we will link github to vs code. This guide shows how to link the account, however you don’t need to create a repository just yet as that will be done in a later stage.

Creating the webapp

Now, from the Azure extension, we will select the “create new webapp” button. This should walk you through the motions of creating the application in azure as well as creating a new repository in Github.

Once this is done, you can go to source control and create an empty commit. This will publish your webapp to Azure.

If you go to your Azure portal under static webapps, you should see the url for your webapp.

Getting PowerBI ready

Creating a dataset

For this purpose, we’re going to create a small dataset in PowerBI with some data that we will later aggregate in order to show some KPIs.

The Dataset

I created an excel sheet with some sample data.

Year	Sales	Customers
2022	20000	120
2021	25000	130
2020	21000	150

I imported the dataset into PowerBI and grabbed the ID from the URL as we’re going to need it later.

Enabling REST

In order to use the PowerBI REST apis, we will need to enable it first. We do this by registering the app with Azure. Here is a guide on how to do this. We will use the embed for my organization option and once you’re done, you should have an app id and secret. When selecting the APIs that will be available, you can select Read all reports and Read all datasets.

Adding Endpoints

This would have created an app registration for you in Azure. Go into your apps (under Home -> App Registrations) and you should see it there. Add the endpoints for testing your app. Ultimately, you will be adding your webapp’s final domain to it.

Building the App

Sample App

For the purposes of this demo I used a sample application that already uses the Azure MSAL node modules. You can see the app here: https://github.com/AzureAD/microsoft-authentication-library-for-js/tree/dev/samples/msal-react-samples/react-router-sample. What I did was clone the app and then publish it to my github repository.

Authentication configuration

The authConfig.js file contains information about the authentication. Modify the Scopes in line 57 to include the following:

`1`	`["User.Read",` `"Mail.Read",` `"https://analysis.windows.net/powerbi/api/Dataset.Read.All"]`

Add the PowerBI endpoint to the protected resources.

1pbiDataset: {
2    endpoint: "https://api.powerbi.com/v1.0/myorg/datasets",
3    scopes: ["https://analysis.windows.net/powerbi/api/Dataset.Read.All"]
4}

Adding a PowerBI tab.

The above application has a tab named Tenant. It makes use of the REST API through fetch. We will duplicate this and modify the fetch.js file a bit to handle the POST requests that PowerBI needs to make.

Modifying fetch.js

Go to src/fetch.js of your sample application and modify the file as such:

1/*
2 * Copyright (c) Microsoft Corporation. All rights reserved.
3 * Licensed under the MIT License.
4 */
5 
6/**
7 * Makes a GET request using authorization header. For more, visit:
8 * https://tools.ietf.org/html/rfc6750
9 * @param {string} accessToken 
10 * @param {string} apiEndpoint
11 * @param {string} method
12 * @param {json} body
13 */
14export const callApiWithToken = async(accessToken, apiEndpoint, method = "GET", headers = new Headers(), body) => {
15    //const headers = new Headers();
16    const bearer = `Bearer ${accessToken}`;
17 
18    headers.append("Authorization", bearer);
19 
20    const options = {
21        method: method,
22        headers: headers,
23        body: JSON.stringify(body)
24    };
25 
26    return fetch(apiEndpoint, options)
27        .then(response => response.json())
28        .catch(error => console.log(error));
29}

By doing this, we’re adding the capability to modify the method, the headers, and submit a body.

Creating a KPI tab

Go to the Tenant.jsx file, duplicate it, and rename it to KPIs.jsx. We will be modifying it as below. The modifications include calls to the newly configured PowerBI endpoints, creating KPI boxes from the PowerBI data, and coloring the boxes.

1import { useEffect, useState } from "react";
2 
3import { MsalAuthenticationTemplate, useMsal, useAccount } from "@azure/msal-react";
4import { InteractionType, EventType } from "@azure/msal-browser";
5 
6import { loginRequest, protectedResources } from "../authConfig";
7import { callApiWithToken } from "../fetch";
8import Button from "react-bootstrap/Button";
9import Card from "react-bootstrap/Card";
10import Row from "react-bootstrap/Row";
11import Col from "react-bootstrap/Col";
12 
13const body = {
14    "queries": [
15        {
16            "query": "EVALUATE ALL(Sheet1)"
17        }
18    ]
19}
20 
21const bgColors = ["#219ebc","#023047","#fb8500"];
22 
23const KPIContent = () => {
24 
25    /**
26     * useMsal is hook that returns the PublicClientApplication instance, 
27     * an array of all accounts currently signed in and an inProgress value 
28     * that tells you what msal is currently doing. For more, visit: 
29     * https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-react/docs/hooks.md
30     */
31    const { instance, accounts, inProgress } = useMsal();
32    const account = useAccount(accounts[0] || {});
33    const [datasetData, setDatasetData] = useState(null);
34 
35 
36 
37    useEffect(() => {
38 
39 
40        /**
41         * In order to get the direct response from calling acquireTokenRedirect() API, register an event
42         * and listen for ACQUIRE_TOKEN_SUCCESS. Make sure to remove the event once component unmounts. For more, 
43         * visit: https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-react/docs/events.md
44         */
45 
46        // This will be run on component mount
47        const callbackId = instance.addEventCallback((message) => {
48            // This will be run every time an event is emitted after registering this callback
49            if (message.eventType === EventType.ACQUIRE_TOKEN_SUCCESS) {
50                const response = message.payload;
51                const headers = new Headers();
52                headers.append("Content-Type", "application/json");
53                // Do something with the response
54                callApiWithToken(response.accessToken, protectedResources.pbiDataset.endpoint + '/ac6ea259-x1e4-4329-8ae4-fc0368fa3668/executeQueries', "POST", headers, body)
55                    .then(response => { console.log("requesting"); setDatasetData(response); console.log(response); });
56            }
57        });
58 
59 
60        return () => {
61            // This will be run on component unmount
62            if (callbackId) {
63                instance.removeEventCallback(callbackId);
64 
65            }
66 
67        }
68    }, [account, inProgress, instance]);
69 
70    const requestDatasetData = () => {
71        instance.acquireTokenRedirect({
72            scopes: protectedResources.pbiDataset.scopes,
73        }).catch(error => console.log(error))
74    }
75 
76    return (
77        <>
78            <Row className="align-items-center mb-5">{datasetData ? datasetData.results[0].tables[0].rows.map((kpi, i) => {
79                return <Col className="d-flex justify-content-center" key={i}>
80                    <Card style={{ color:"white", width: '18rem', backgroundColor: bgColors[i] }}>
81                        <Card.Body>
82                            <Card.Title>${kpi["Sheet1[Sales]"]}</Card.Title>
83                            <Card.Subtitle className="mb-2 text">Year: {kpi["Sheet1[Year]"]}</Card.Subtitle>
84                            <Card.Text>*Sales for {kpi["Sheet1[Customers]"]} customers.</Card.Text>
85                        </Card.Body>
86                    </Card></Col>
87            }) : "nothing yet"}</Row>
88            <div className="row justify-content-center">
89                <Button variant="secondary" onClick={requestDatasetData}>Request Dataset Data</Button>
90            </div>
91        </>
92    );
93};
94 
95/**
96 * The `MsalAuthenticationTemplate` component will render its children if a user is authenticated 
97 * or attempt to sign a user in. Just provide it with the interaction type you would like to use 
98 * (redirect or popup) and optionally a [request object](https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-browser/docs/request-response-object.md)
99 * to be passed to the login API, a component to display while authentication is in progress or a component to display if an error occurs. For more, visit:
100 * https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-react/docs/getting-started.md
101 */
102export const KPIs = () => {
103    const authRequest = {
104        ...loginRequest
105    };
106 
107    return (
108        <MsalAuthenticationTemplate
109            interactionType={InteractionType.Popup}
110            authenticationRequest={authRequest}
111        >
112            <KPIContent />
113        </MsalAuthenticationTemplate>
114    )
115};

Run your application

Type in npm run build in your VS terminal and a browser should popup with your application. Navigate to the “KPIs” tab that you created and click on “Request Dataset Data”. It should go through the process of authenticating you and you should be able to see your KPIs live fed from PowerBI!

Conclusion

There you have it! Although this was a simple example, you can easily see how we’re able to fully manipulate the way we want to show PowerBI’s data. This can be used in a number of applications, not just web. Can you think of any other applications? Do you find this useful? Reach out to me on LinkedIn!

Create a React app that shows PowerBI data using REST

by Roberto Trevino on April 4th, 2022 | ~ minute read