ActivityInfo DocumentationGeneral 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.

4. Merging multiple forms in Power BI

Do you have multiple records which have had the location types changed a couple of times?  Power BI can only return information for one of the locations and the API tries to be smart with returning the columns you want but, unfortunately its not smart enough to look for locations in older location types. 


The solution is to explicitly define all columns you need as query parameters in the URL to the API. Depending on the number of indicators and attributes you may have this might not be very convenient. 

The second option is to fetch the location data in a separate query and then to combine the results using the merge function in PowerBI. 

https://www.activityinfo.org/resources/form/a0000000000/query/rows?id=_id&location.id=location&location.name=location.name&lat=location.point.latitude&lon=location.point.longitude&District=location.admin.name&Governorate=location.admin.parent.name&location.alternative.name=location.axe

In PowerBI, you can then merge the "id" column with the site id column  (this may be called "@id" and will contain values such as "s0000100000 ) to attach the deatiled location information to your table.

  1. Select both queries
  2. Click "Merge Queries" in the top menu
  3. Select the correct columns in the dialog window which pops up
  4. Select the correct join type (make sure that you keep all records in the table from the original query with the short URL) then click ok
  5. Click the new column with the yellow header to select which columns to expand into the final result. 

In our example we chose to remove the location .* columns from the query with the short URL because most were null anyway.