Power BI User Guide - CartONG

1 CartONG 23 boulevard du mus e, 73000 Chamb ry France | | Page 1 | 40 Power BI USER Guide Lessons learned from a UNHCR- CartONG Power BI project: different ways of connecting to various sources, of cleaning the data structure and of creating reports for further publishing or sharing. Legend: screenshot of the Power BI Dashboard on Anthropometry and Public Health data built by CartONG in support of UNHCR, which can be consulted here: Power BI User Guide | Page 2 | 40 This publication has been produced with the assistance of the Office of the United Nations High Commissioner for Refugees (UNHCR).

2 The content of this publication is the sole responsibility of CartONG and is not reflecting the views of UNHCR in any way. This publication is supported by the French Development Agency (AFD). Nevertheless, the ideas and opinions presented in this document do not necessarily represent those of AFD. Power BI User Guide | Page 3 | 40 Contents Summary .. 4 I. What are Power BI tools .. 4 General overview .. 4 PowerBI Desktop Workflow .. 5 II. Connecting Power BI .. 5 Type of Data Source .. 5 Data Source Connection Workflow .. 6 Connecting an Excel file stored on cloud-based service .. 7 Dropbox.

3 7 Google Drive .. 9 OneDrive .. 10 Sharepoint .. 12 Connecting Enketo form on Kobotoolbox .. 14 Find Kobo project ID through Power BI Desktop .. 14 Get form data .. 14 Get form labels instead of code .. 15 Connecting database .. 16 Comparison table .. 19 III. Preparing an Excel data .. 20 The list of recommendations .. 20 Working on the modified structure of Excel database .. 22 How manually add or update the data .. 23 Generate key .. 23 Fill in with data all sheets .. 24 How to modify a Cascading list .. 24 How to import data .. 26 Load data from KoboToolbox .. 26 Upload data to Excel database .

4 28 How to filter data across the tabs .. 28 IV. Cleaning and transformation of Kobo data .. 29 List of transformations .. 29 Direct connection to ArgGIS .. 31 V. Publish dashboard from PowerBI desktop to PowerBI service .. 38 Power BI User Guide | Page 4 | 40 Summary Microsoft Power BI is a powerful analytical platform that provides the user with tools for analyzing, visualizing, and sharing data. The main purpose of this document is to present the different ways of connecting to various sources, cleaning the data structure, and creating the report for further publishing to the web and sharing with the colleagues.

5 I. What are Power BI tools General overview Power BI includes Power BI Desktop, Power BI Service, and PowerBI Mobile. Power BI desktop is the Windows-desktop-based application for PCs and desktops. Power BI Service is the online service accessed via Also, Power BI offers a set of mobile apps for iOS, Android, and Windows 10 mobile devices. In mobile apps, you connect to and interact with your cloud and on-premises data. Power BI Desktop works in conjunction with the Power BI Service. Power BI Desktop allows you to do the following: Get the data from a variety of sources Create relationships between your data and enrich your data model Create and save your reports Upload or publish your reports and share them with your colleagues.

6 It s recommended to use the PowerBI Desktop for creating reports/dashboards and Power BI service for publishing to the web and sharing a dashboard with others. Power BI User Guide | Page 5 | 40 PowerBI Desktop Workflow There are three main core areas in PowerBI Desktop: Data Preparation Data Modeling Data Visualization The data preparation part includes the ways of connecting the source files with the Query Editor and working with this data to get the actual dataset, the data we want to analyze later. The data is loaded from Query Editor to Data model. The Data model consists of two parts: Data Modeling and Data Visualization.

7 Data Modeling is performed in Data View and Relationship View. Data Modeling is the part where the data analysis takes place and all the visuals are added. II. Connecting Power BI Type of Data Source PowerBI Desktop allows to connect to data from many different sources: File, such as excel, text/CSV, XML, JSON; database , such as SQL server database , postgresql database , Access database ; Online services, such as SharePoint Online List, Microsoft Exchange Online, Google Analytics; Data collection platform, such as KoboToolbox, SurveyCTO. This document contains examples of connecting: Excel file saved on cloud-based services to Power BI.

8 Dropbox Google Sheet OneDrive Power BI User Guide | Page 6 | 40 Sharepoint Xls form on Kobotoolbox postgresql database It s essential to understand the performance of each data source and also connection methods, report, and dashboard creation and the most important how easily data updates from the data source Data Source Connection Workflow The following diagram presents the data source connection workflow from the data preparation in PowerBI Desktop to Publishing the report to the web and scheduling the Refresh in PowerBI Service. All of the steps are explained for each data source in the further sections.

9 The common steps which could be applied for all data source: 1) Hosting the file 2) Data preparation (PowerBI Desktop) Building an URL by following the template Transforming data Data processing 3) Creating a report (PowerBI Desktop) 4) Publishing a report to the web (PowerBI Service) 5) Scheduling Refresh (PowerBI Service) Power BI User Guide | Page 7 | 40 Connecting an Excel file stored on cloud-based service It s highly recommended to modify the structure of Excel database based on recommendations in the section Preparing an Excel Data Dropbox More information on Dropbox Perform the following steps to connect an excel file hosted in Dropbox to Power BI Desktop/Service 1.

10 Save your file on Dropbox and choose Share > Copy link 2. Paste the link in any editor and replace the end dl=0 with dl=1 Example (Before) Example (After) 3. Open Power BI desktop, click Get Data > From Web and copy the newly created Url, then enter your credentials 4. The query is loaded Power BI User Guide | Page 8 | 40 5. Right-click on the file icon and choose Excel, then filter by column Name (select Table 1) 6. Click on Data column and choose to Remove Other Columns 7. Click on Expand 8. Clean the data and modify the data structure 9. Create a dashboard and publish to PowerBI (section Publishing to PowerBI service ) 10.