User Manual Tips and tricksGuide to setting up ActivityInfo and Power BI

Guide to setting up ActivityInfo and Power BI

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.

First of all you need to open Power BI, if you don't have it installed then you can download here and create an account.

Once the download is completed, you will need to sign into your Power BI account.

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.

1. Obtain the URL for your form

Before you connect Power BI to your form in ActivityInfo, you will need to obtain the URL of the API endpoint for your form.

1.1. Find the form identifier

For classic forms, you will find the identifier when you select the form in the Design tab. The identifier is shown in the ID field. Also note the reporting frequency of your form. The example below shows a form with identifier 79651 with a monthly reporting frequency.

Find a form's identifier in the Design tab

1.2. Construct the URL to your form

The URL of the API endpoint for forms in ActivityInfo looks like https://www.activityinfo.org/resources/form/{REF}/query/rows where {REF} should be replaced with a reference to your form. This reference is constructed as follows:

  • If your form has monthly reporting, take the form identifier and prepend it with "M". For example: if your form has identifier 79651, {REF} will be "M0000079651".
  • If your form as reporting once, take the form identifier and prepend it with "a". For example, if your form has identifier 79651, {REF} will be "a0000079651".

Another example: if you are targeting a form 99 with reporting "once", then your url will look like this: https://www.activityinfo.org/resources/form/a0000000099/query/rows

[REF] has to have 10 digits  after the 'a'  or 'M' 

2. Connect ActivityInfo and Power BI

To connect ActivityInfo to Power BI, log into Power BI and follow these steps:

  1. click "Get Data" and select "Web",
  2. in the dialog window, select the "Basic" option and enter the URL for the API endpoint for your form (see below) and click "OK",
  3. a new dialog window with title "Access Web content" appears: select "Basic", enter your ActivityInfo login credentials and click "Connect".
3. Convert list result to table format

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.

3.1. Convert list result to table

  1. Click "To Table", a pop-up will appear inviting the user to select or enter delimiters and how to handle extra columns.
  2. Accept the default settings by clicking "OK".

3.2. Select columns

  1. untick the "Use the original column name as prefix",
  2. click "OK".

3.3. Make final changes to the table

You can make further changes to your table as shown below.