Power BI tutorials and tips

Connecting Kobo to Power BI using Kobo API v2

1. To connect PowerBI to the Kobo API, start by going to your Kobo account to locate the form ID (uuid). Select the project you wish to connect to from your list of deployed projects. From here you can get the form ID (uuid) from the URL. The URL of the page you are currently on will follow this format:

https://kobo.unhcr.org/#/forms/{uuid}/landing

Copy the string of characters between the two slashes. See below for an example.

2. To make a web data source for your form, paste the form ID (uuid) into the following URL. Please note that the curly brackets should not be included.

https://kobo.unhcr.org/api/v2/assets/{uuid}/data/?format=json

3. To connect to PowerBI, open a blank powerBI report. In the Home menu choose Get Data and from the dropdown choose Web.

Next paste the URL from step 2. Make sure that you select Basic authentication and enter your KoBo Toolbox credentials correctly. From the drop-down menu, you can select that these credentials apply only to the selected form (the item at the bottom).

4.Click connect. This should take you to the Power Query Editor. You can right-click the query to rename it. Before proceeding further, you need to convert the data into a table.

The only thing left to do is to expand the columns. You can de-select the option to use the original column names as a prefix.

5. Your data is now ready for analysis. Each time you open the .pbix file, make sure you refresh, so that you know that you are working with updated data.

Please note that you can use a similar method to connect Power Query to KoBo. Just click on the "Data" section of Microsoft Excel, and select Get Data -> From other sources -> From web.

Repeating Groups

Think of repeating groups as something similar to a relational database, where multiple records in a repeating group correspond to a single record in the main query (one to many relationship)

1. Similar to our example with multiple choice questions, we will start with duplicating the original query and renaming it into something more descriptive (RepeatingGroup for example)

2. After this is done, select and remove all columns, except the repeating group and _id (at this point the repeating group still needs to be expanded, and is shown as a "list").

3. Click to expand the list to new rows. After this, you should get a different query (probably with a larger row count), where all records of that repeating group are shown

Make sure to properly format (right click -> change type) all relevent fields (whole numbers, decimals, dates etc.).

4. Next, close & apply the query editor, and head for the Relationships section of Power BI. You should be able to see 3 queries here. Double click the relationship between the main query (KoboData) and the repeating group (RepeatingGroup), and make sure that you select both from the cross direction filter drop down list. This will enable you to filter by values from the newly created query.

You can now use the values from the repeating group query for data analysis and visuals.

You could have multiple repeating groups in a single form. Just repeat these steps for all groups and make sure that proper relationships are set.

Retrieving KoBo records for editing using Power BI

Using Power BI, it is possible to access KoBo records for editing, without needing to go through the KoBo back-end. To do this, you need a table visual to display the records, and a custom column to generate the link to the record.

1. To create the custom column, you need to open your report (that is using KoBo Toolbox as a data source) in Power BI Desktop, go to query editor and click on Add Columns -> Custom Column. You will be using 2 columns generated by KoBo [_xform_id_string] and [_id]. Make sure that these columns have not been removed in Power BI, or that the names have not been changed (a prefix added for example). If the name has been changed, you will have to reference them using the new name.

2. This is the formula that you need to paste into the Custom column formula area (copy the entire formula):
"https://im.unhcr.org/kobosupport/link/index.php?xformid=" & [_xform_id_string] & "&recordid=" & Text.From([_id])

[_xform_id_string] refers to the form id. The field-name is the same for every form. (make sure that the column has not been removed from the Power BI report, and that the name has not been changed)

[_id] refers to the record id. The field-name is the same for every form. (make sure that the column has not been removed from the Power BI report, and that the name has not been changed)

The rest of the link refers to a PHP script that will prompt you to log in, pass the IDs to KoBo, and redirect you to the record.

3. Next, you need to return to the dashboard area, and create a table visual with the columns you wish to display, and the custom column that you just created.

4. By clicking on the custom column in the fields area, and going to the modeling tab in the top menu, you can change the formatting of the column by selecting Data Category -> Web URL.

5. In the formatting area of the visual, under values, you can set URL icon to on, which will change the link into an icon.

6. That's it. Now, you just need to click on the link, which will take you to a log-in screen where you need to enter your kobo credentials. After that, you will be able to edit the record.

Security Tips and Best Practices

Here you can find some of the best security practices related to using Power BI to analyze Kobo data.

1. Never share the credentials of the project owner account. This account has full access to the to submit, view, edit and validate the data, and make changes to the form. In case you need to share the project (data or form), be sure to provide the minimum set of permissions required (for example, if the user needs to view the data or use it for data visualization in Power BI, only select "View submissions". If the user is an enumerator, only select "Add submissions").

Be mindful that if a user has the "view data" permission, that user can see the entire data of that project.

2. Do not check the "share data publicly" option in the project sharing settings as this is not a requirement for connecting Power BI to KoBo. Also, when using Power BI, be sure to remove all sensitive fields (like names, addresses etc.) in the query editor. Just because the sensitive information is not displayed in the charts, users might still be able to access it using right click -> view records unless the columns are completely removed.

Useful Links

UNHCR Kobo Toolbox Server

Kobo Toolbox API documentation

Raw Internal Data Library (RIDL)

Overview of GIS capabilities in PowerBI

There are several visualizations in PowerBI that allow you to add a map on the page and visualize spatial data. Depending on your needs, you will have to use one or another and you might not be able to do everything you want in terms of styling and data interaction. In some cases, you might want to consider using ArcGIS Online that offers more mapping capabilities but less possible data sources and fewer data manipulation and relationships capacities. Mapping visualizations in PowerBI are in constant evolution and each has advantages and disadvantages.

In this guide, we will see the three visualizations that we recommend: Shape Map, Mapbox Visual and ArcGIS Maps for PowerBI.

• Shape Map is a simple way to produce choropleth maps, a type of thematic map in which a set of pre-defined areas is colored in proportion to a statistical variable. This visual is particularly useful to create maps at the scale of administrative divisions within a country, as shown in the example below. The disadvantage is that you will not be able to add a basemap, it will only display the areas on a white background.



• MapBox Visual is a more advanced visualization that allows you to produce choropleth maps, proportional symbol maps and heatmaps. It is also possible to use our standard UNHCR basemap, that complies with the UN recognized countries and borders.



• ArcGIS Maps for PowerBI allows you to produce proportional symbol maps and the main advantage is that you can add any Map or Layer from ArcGIS Online on top of it for context.

We do not recommend the use of the visualizations Map and Filled Map as none of their basemaps comply with the UN recognized countries and borders. The MapBox Visual should be used instead, which can achieve the same results with even more functionalities.

Shape map

Shape Map is not accessible by default in PowerBI. To enable it, go to File > Options and Settings > Options > Preview Features, then select the Shape map visual checkbox. You'll need to restart Power BI Desktop after you make the selection.

To be able to use the shape map visual, you first need the boundaries of the areas to be colored in a file with the format .json. We will see how to use the Core GIS Database to produce this file but you can create from any other areas providing that you have them in a spatial format (Shapefile, GDB, GeoJSON etc.)

1. Export the Core GIS Database by going to https://im.unhcr.org/geoservices/export/index.html
2. Select the dataset and the location. For the example, we will choose the First Administrative Divisions of South Sudan and export in GeoJSON.





3. Go to https://mapshaper.org/ and click on Select





4. Open the .geojson file that you downloaded before and click on Import





5. The boundaries should open on the map. Click on Export on the top-right corner, select TopoJSON and click on Export





6. You now have the boundaries in the proper format .json, ready to be used in a shape map in PowerBI. You can convert any other file to .json using Mapshaper if you are working with areas that are not part of the GIS Core Database.

Using the UNHCR GIS Core Database Export Tool, you can also download the list of administrative divisions as a .csv file to start entering your data per area :

For the example, we will work directly on this.csv file and import it in PowerBI, but you can of course get the data from a different source, as long as you use the same list of areas both in the .json file and the actual dataset to be mapped.

We added for the example two columns: one with numeric values (e.g. number_of_X) and one with text values (e.g. type_of_X) to show in PowerBI how both situations work.

In PowerBI, we start by adding our dataset, in that case opened directly from the .csv file

Add a new Shape Map on the page:

In location, drag the field containing the unique identifier of each area. In our case, it is the pcode. It will display by default the map of the United States of America but we will change that just after:

Go to Format > Shape > + Add Map:

Open the .json file created earlier with Mapshaper:

If you want to map a numeric value :

1. Place your numeric value in Color Saturation:





2. Set the colors in Format > Data Colors

If you want to map a text value :

1. Place your text value in both Legend and Color Saturation:





2. Set the colors in Format > Data Colors

For more information about the Shape Map visualization, please consult the official documentation here : https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map

Mapbox Visual for PowerBI

Prior to start using the Mapbox Visual, contact the GIS Support Unit at mapping@unhcr.org who will provide you with a Mapbox access token and the proper URLs to use for styling. This token is mandatory to start using the visual.

The Mapbox visual is not present by default in PowerBI. To add it, click on Get more visuals:

Search for mapbox and click on Mapbox Visual:

Click on Add:

Creating a map with Proportional Circles :

Prepare your data with longitude and latitude columns and a numeric value to be mapped (e.g. number of refugees). You can use the UNHCR GIS Core Database Export Tool to get the locations coordinates in a .csv file, or connect to any other data sources that contains coordinates.

Below is an example of file with population data per refugee site. Coordinates are included in the columns latitude and longitude.

Add your data source and place the latitude and longitude in the appropriate inputs :

In Format visual > Viz Settings set as below :

• Access Token: provided by GIS Support Unit
• Map Style: Custom...
• Style URL: provided by GIS Support Unit

Place the numeric value you want to be represented by proportional circles in Size :

Change the color and default radius of the circles in Format Visual > Circle as shown below :

Put in Tooltips the attributes you want to appear when hovering the circles with the cursor :

Creating a map with colors per type :

Follow the same steps as for proportional circles and add the attribute you want the color to be based on in Color :

If you are visualizing a text value (e.g. Type), set the colors in Format visual > Data colors :

If you are visualizing a numeric value (e.g. population), set the colors in Format visual > Circle and create a gradient by setting the Minimum and Maximum color. Tick Diverging to add an intermediate color :

Bonus: You can add on the map one of the layer of the GIS Core Database in Format > Raster. Select one of the URL below and paste it in URL.

Unfortunately, you can only add one layer at the time and can't filter by country (you have to display for the whole world). You can add the legend as an image by downloading it first here : Legend for UNHCR Presence | Legend for UNHCR People of Concern Locations

Creating a choropleth map per country :

Prepare your data with iso3 columns per countries to be mapped. You can use the UNHCR GIS Core Database Export Tool to get the list of iso3 per country, or connect to any other data sources that contains iso3.

Add a Mapbox visual and place the attribute iso3 in Location and the attribute you want the color to be based on in Color.

In Format visual > Viz Settings set as below :

• Access Token: provided by GIS Support Unit
• Map Style: Custom...
• Style URL: provided by GIS Support Unit

In Format visual, untick Circle and tick Choropleth:

Open Choropleth and set as below :

• Data Level 1: Custom Tileset
• Vector Tile Url Level 1: provided by GIS Support Unit
• Source Layer Name Level 1: provided by GIS Support Unit
• Vector Property Level 1: color_code

If you are visualizing a text value (e.g. Type), set the colors in Format visual > Data colors :

If you are visualizing a numeric value (e.g. population density), set the colors in Format visual > Choropleth and create a gradient by setting the Minimum and Maximum color. Tick Diverging to add an intermediate color :

To create choropleth maps at a sub-national level (by administrative divisions), please contact the GIS Support Unit who will set up the proper Styles and Layers in Mapbox and then follow the same steps as for the country choropleth map for the customization of the colors.

For more information about the Mapbox visual, please consult the official documentation here : https://docs.mapbox.com/help/tutorials/power-bi/

ArcGIS Maps for PowerBI

Add a new ArcGIS Map and click on Sign In.

Chose Your ArcGIS organization's URL and enter the URL : unhcr.maps.arcgis.com.

Click on UNHCR Credential login and use your default UNHCR credentials (email and password).

You should then be signed in, as shown on the picture below.

Create a map with proportional circles

Prepare your data with longitude and latitude columns and a numeric value to be mapped (e.g. number of refugees). You can use the UNHCR GIS Core Database Export Tool to get the locations coordinates in a .csv file, or connect to any other data sources that contains coordinates.

Below is an example of file with population data per refugee site. Coordinates are included in the columns latitude and longitude.

Add your data source and place the latitude and longitude in the appropriate inputs :

Place the numeric value you want to be represented by proportional circles in size :

To change the color and size of the circles, open the symbology as shown on the picture below :

Select Counts and Amounts (size) and click on Style Options :

Open Symbology and click on Advanced options :

Set here the color :

Back in the Symbol Style menu, untick Classify Data to create true proportional circles:

Changing the basemap

To change the basemap, go to Format Visual > Map Tools and tick Basemaps:

On the map, click on the Basemap icon on the left side and select the basemap you want to use. We recommend you to use the one called UNHCR Basemap White EN:

Adding any map or layer from ArcGIS Online

The main added value of the ArcGIS Maps visualization is that you can add any map or layer from ArcGIS Online in it.

Click on the Settings icon on the left side > Reference Layer :

Navigate through the contents of ArcGIS Online by using the different options (My Contents, My Organization, My Groups etc.). Once you find the map or layer to add, click on the + sign to add it on the map. See below an example to add the UNHCR Presence and People of Concern Locations but you can create any map or layer first in ArcGIS Online, and then add it like this.

For more information about the ArcGIS Map for PowerBI, please consult the official documentation here : https://doc.arcgis.com/en/power-bi/get-started/about-maps-for-power-bi.htm

How to publish and share Power BI reports

All staff with a UNHCR account can download Power BI Desktop and start developing dashboards and reports. In case you need to share your report with others, you (or someone in your team) need to have:

- a Power BI PRO account, which enables publishing reports;

- a Premium Workspace, where the PRO user can share reports with the rest of the team/operation (and host the operational dashboards in general).

1. Sharing reports:

- In order to share a report that is published on a Workspace (can be personal, regular or premium), the user needs to have a PRO account

- Colleagues at DIST manage all Power BI PRO Licenses and requests should be channeled through UNHCR’s Global Service Desk - you can find the step-by-step guidance for requests here. Currently, there is a cost associated with each pro account – so it is recommended to start by requesting 1 per operation/team.

- When requesting, specify the reason/purpose and describe the reports and data sources (e.g. I need a PRO account in order to share Report XYZ with the rest of my operation. The data is collected through a combination of excel and Kobo Toolbox)

- PRO users can share with other PRO users (do not have to be part of the same organization). In order for a PRO user to share reports with FREE users (that are part of the same organization), the user that wants to share the report needs to have access to a PREMIUM WORKSPACE

2. Premium workspace (sharing with FREE users):

- PRO users can share with FREE users if they have a PREMIUM WORKSPACE

- Premium WORKSPACEs should also be requested through the Global Service Desk

- The PRO user would have to publish a report on that PREMIUM WORKSPACE and then, from there, share the report with specific users that are part of the organization (UNHCR), or the entire organization. The user that is sharing will still need a PRO account.

3. Publishing to the web (external link):

- PRO user needs to be added to the Power BI security group for publishing reports to the web (requested should be sent to the Global Service Desk)

- When requesting, specify the reason/purpose and describe the reports and data sources (e.g. I need to embed a 3w report in a public portal. The data is collected and stored in Activity info)