Connecting Power BI to ActivityInfo
Users can link ActivityInfo to Power BI through the API. In this short guide we will explain how you can setup the basis, so the data from ActivityInfo goes directly to Power BI.
Once the download is completed, you will need to sign into your Power BI account.
The Query API Response Size limit is 32 MB. To reduce the data size you can do the following:
- reduce the number of columns included in the query
- shorten the labels of columns
Power BI is a product provided by the Microsoft Corporation for which we do not provide support. Please consult the Power BI website at https://powerbi.microsoft.com to learn more about how to use the product.
- Before you connect Power BI to your form in ActivityInfo, you will need to obtain the URL of the API endpoint for your Form.
- You can find the identifier of a Form when you open the Form in the Table View page. The identifier is shown in the URL. But you can also get it from the Export options. Before getting the URL select the Columns that you want to use. You can also customise the query by selecting and renaming columns via "Select Columns".
- In the Table View page of your Form, click on "Export" to reveal the drop-down menu and click on "Export via the API".
- From the new options click on "Query JSON using a URL".
- Copy the URL that appears.
If you have an ActivityInfo password, you may use this to authenticate with our API from Power BI as explained in step 3 below. If you do not have an ActivityInfo password, then you need to generate a personal API token in your ActivityInfo account as explained in the Using an API token to authenticate with ActivityInfo article.
The option to authenticate with the ActivityInfo API using your ActivityInfo password is scheduled to be removed in March 2021, therefore we recommend that you use a personal API token instead.
To connect ActivityInfo to Power BI, log into Power BI and follow these steps:
- Click "Get Data" and select "Web",
- In the dialog window, select the "Basic" option, paste the URL and click "OK",
- A new dialog window with title "Access Web content" appears: select "Basic", enter your ActivityInfo login credentials and click "Connect". If you are using a personal API token, you may enter your email address as your user name and copy the API token in the password field.
- After you have connected your form to Power BI, the Query Editor will open and display a "List" as the result. The following steps explain how to convert this list to a table.
Convert list result to table
- Click "To Table", a pop-up will appear inviting the user to select or enter delimiters and how to handle extra columns.
- Accept the default settings by clicking "OK".
- Untick the "Use the original column name as prefix"
- Click "OK"
Make final structural changes to the table
- You can make further changes to your table as shown below. For example, re-order the columns, remove columns, re-name the columns etc.