WIXLIB

Power BI User Guide Select [Add data sources to use the gateway]II.6 Comparison tableThe table below provides with pros and cons of each method with some recommendationson using it for the different projectsData sourcePBI skillsDataProtectionRestrictedAccess to thedatasetAdditional DatatransformationDB Size andcomplexityProjectDropboxBasicpotential risk:ashareablelinkwhichpubliclyavailablepotential risk:It required toapplytheadditional stepsto get data in areadable formatSmall,complexShort-termthe flat tableneedstobeconverted in aproper formatMediumMid-termBig, complexLong-termGoogle IntermediatePostgreSQLAdvanceda file could beaccidentallydeleted, toomany peoplehave accessto itnotIt supportinfo@cartong.org www.cartong.orgPage 19 40

Power BI User GuideIII. Preparing an Excel dataIII.1. The list of recommendationsThe following is a list with the most common changes that need to apply before connectingdata to PowerBI1. Modify the structure of the table to plain table Leave at the top name of the columns Remove merged columns, such as, Acute Malnutrition based on WHO Standards zscores (children 6-59 months of age); Stunting based on WHO Standards z-scores(children 6-59 months of age) Rename the columns by adding a shorter name Column Suggestions Nature of context - protracted 3 yrs, Rename the column (Ex, "Nature ofnon-protracted 3 yrscontext") Refugee, Not refugee, mixed Rename the columnbeneficiaries")(Ex,"Typeof2. For each column add the proper date type (date/text/number) ColumnSAM z-score and/or oedema,% SuggestionsChange data format: Number Remove: text, ‘n.a’ category, sings as %(move to an additional column if needed) U5 population in the surveyed area (in red Change data format: Numberfrom popstats) Remove: text, ‘n.a’ category, sings as %(move to an additional column if needed)3. Apply data validation rules by adding drop-down list and cascading selection Column Suggestions Region Drop-down list Country Drop-down list (Connection betweenRegion and Country, Cascading list) Location Drop-down list (Connection betweenRegion Country Location, Cascadinglist) Month A drop-down list of months and split intotwo columns ( start and end period) Refugee, Not refugee, mixed Drop-down list (Refugee, Not refugee,mixed, Survey on Older people 60,Unregistered refugees)info@cartong.org www.cartong.orgPage 20 40

Power BI User Guide4. Format your data as a table5.Due to a large number of headers, split the table into smaller tables for each section,copy/paste each table to new tabs by linking them together by unique Key fieldNote. Please use a meaningful name for each table. It will simplify the process ofconnecting data to PowerBIinfo@cartong.org www.cartong.orgPage 21 40

Power BI User GuideIII.2. Working on the modified structure of Excel databaseList of modules is presented below-Instructions-Key Location Date-General Info-Acute Malnutrition WHO-Acute Malnutrition NCHS-MUAC Malnutrition-Stunting WHO-Stunting NCHS-Underweight-Overweight-Anaemia childre 6-59-Anaemia children 6-23-Anaemia women 15-49-Mortality rate-VA measle diarrhoea BSFP-U5info@cartong.org www.cartong.orgPage 22 40

Power BI User Guide-IYCF-Food secutiry module-Wash module-Mosquito net module-Trend Sheet-Survey info-GAM stunting anaemia scenatio-SOWC-Secure Info-Drop-down list (Maintenance tab)-Location list (Maintenance tab)III.3. How manually add or update the dataIII.3.A. Generate key1. Go to Key Location Date Tab2. Add a New Key- Add a new row by expanding the table- add all the necessary information:- Region (drop-down list generated based on values from Location list sheet)- Country (drop-down list generated based on values from Location list sheet)- Location (drop-down list generated based on values from Location list sheet)- If other, specify a main location (free text)- Does the data cover multiple sites? (Yes/No)- Year (Number field, the value should be greater than 1990)- End Month (Optional field, drop-down list generated based on values from Drop-down list sheet )- Type of beneficiariesIf there is a duplication, the keys would be highlighted with red colorinfo@cartong.org www.cartong.orgPage 23 40

Power BI User GuideIII.3.B. Fill in with data all sheets1. Add a new row by expanding the table2. Select a key from the drop-down list and fill in with dataPlease add a new row on each sheet for a new key, even if there is no data. Don’t add anew row without specifying a keyIII.3.C. How to modify a Cascading listAdd a new Region1. Go to Location List sheet2. Add a new row under Region column [Table name is location list table Region]3.Enter the name of the new regioninfo@cartong.org www.cartong.orgPage 24 40

Power BI User Guide4. Name of Region will automatically appear in a drop-down listAdd a new country1. Go to Location List sheet2. Add a new column3. Go to Formulas Name manager and click on New NameEnter a new name and formula by following the template Name Table[Column]Table name is location list Region Cascadeinfo@cartong.org www.cartong.orgPage 25 40

Power BI User GuideAdd a new location1. Go to Location Listlocation list Country)sheetandfindcountrytable(Tablenameis2. Add a new column3. Go to Formulas Name manager and click on New NameEnter a new name and formula by following the template Name Table[Column]Table name is location list CountryIII.4. How to import dataIII.4.A. Load data from KoboToolbox1. Log into your Kobotoolbox accountinfo@cartong.org www.cartong.orgPage 26 40

Power BI User Guide2. Choose the form and then click on Data Downloads3. Select the “export type” is “XLS” and “values and header format” is “Labels”4. Click on Export and wait until the file is loaded5. Proceed with loading the datainfo@cartong.org www.cartong.orgPage 27 40

Power BI User GuideIII.4.B. Upload data to Excel database1. Open the database and go to Instructions tab2. Click on Import Data button3. Select the file and wait until the data is loadedThe system notifies you if there is any duplication or submission was modifiedIII.5. How to filter data across the tabs1. Go to Key Location Date tabinfo@cartong.org www.cartong.orgPage 28 40

Power BI User Guide2. Filter the data using headers3. Click on apply filters and wait until the data are filters across the tabs4. If you need to reset all the filters, click on the Reset buttonIV. Cleaning and transformation of Kobo dataIV.1. List of transformationsThe following is a list with the most common transformations that need be applied: Rename table(s)Rename fieldsRemove unnecessary fieldsinfo@cartong.org www.cartong.orgPage 29 40

Power BI User Guide Check decimal numbers. Especially check separators defined by system’s regionalsettings. Calculate new field(s)info@cartong.org www.cartong.orgPage 30 40

Power BI User Guide Add measure(s)Create new table(s)Add relationships between the tablesIV.2. Direct connection to ArgGISTo avoid a feature limitation (the service returns a maximum of 1000 features), a direct queryfor each country was created and then they all were merged in the Global table.Also, there is a separate additional file that is linked to Global database where we can addlocations that were not found on ArcGIS service.Here are the steps on how to query data for a specific country (Example. Algeria, code – ALG)1. Please go to Query Editor New Source Blank Queryinfo@cartong.org www.cartong.orgPage 31 40

Power BI User Guide2. Copy the query below by replacing the country code re prod/MapServer/0/query?where iso3%3D%27ALG%27&text &objectIds &time &geometry &geometryType esriGeometryEnvelope&inSR &spatialRel esriSpatialRelIntersects&relationParam &outFields *&returnGeometry true&returnTrueCurves false&maxAllowableOffset &geometryPrecision &outSR &returnIdsOnly false&returnCountOnly false&orderByFields &groupByFieldsForStatistics &outStatistics &returnZ false&returnM false&gdbVersion &returnDistinctValues false&resultOffset &resultRecordCount &queryByDistance &returnExtentsOnly false&datumTransformation ¶meterValues &rangeValues &f geojson"), null, null, 1252)})Note. For private service, you will need to add &token yourGeneratedToken ph covid19 view/MapServer/1/query?where 1%3D1&text &objectIds &time &geometry &geometryType esriGeometryEnvelope&inSR &spatialRel esriSpatialRelIntersects&relationParam &outFields &returnGeometry true&returnTrueCurves false&maxAllowableOffset &geometryPrecision &outSR &returnIdsOnly false&returnCountOnly false&orderByFields &groupByFieldsForStatistics &outStatistics &returnZ false&returnM false&gdbVersion &returnDistinctValues false&resultOffset &resultRecordCount &queryByDistance &returnExtentsOnly false&datumTransformation ¶meterValues &rangeValues &f geojson&token yourGeneratedToken"), null, null, /arcgis/tokens/generateTokeninfo@cartong.org www.cartong.orgneedtogotoPage 32 40

Power BI User Guide3. Transform data to Json format4. Expand column 15. Expand column1.crsinfo@cartong.org www.cartong.orgPage 33 40

Power BI User Guide6. Expand Column1.crs.properties7. Expand Column.features8. Expand Column1feature1info@cartong.org www.cartong.orgPage 34 40

Power BI User Guide9. Expand Column1.Feature.geometry8. Extract values for org www.cartong.orgPage 35 40

Power BI User Guide10. Split column in 2 separate columnsBefore splitting the columns, please duplicate this column. To do so, right-click on the columnname, and select Duplicate Column option from the context menu.Please select the Split Columns and then select By Delimiter option.Please do not rename the columns to avoid the problems with merging the data to GlobaltablesAll the previous steps can be replaced by the query below (you need to replaceonly country code)info@cartong.org www.cartong.orgPage 36 40

Power BI User GuideletSource re prod/MapServer/0/query?where iso3%3D%27DZA%27&text &objectIds &time &geometry &geometryType esriGeometryEnvelope&inSR &spatialRel esriSpatialRelIntersects&relationParam &outFields *&returnGeometry true&returnTrueCurves false&maxAllowableOffset &geometryPrecision &outSR &returnIdsOnly false&returnCountOnly false&orderByFields &groupByFieldsForStatistics &outStatistics &returnZ false&returnM false&gdbVersion &returnDistinctValues false&resultOffset &resultRecordCount &queryByDistance &returnExtentsOnly false&datumTransformation ¶meterValues &rangeValues &f geojson"), null, null, 1252)}),#"Parsed JSON" Expanded Column1" Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"type", "crs","features"}, {"Column1.type", "Column1.crs", "Column1.features"}),#"Expanded Column1.crs" Table.ExpandRecordColumn(#"Expanded Column1", "Column1.crs",{"type", "properties"}, {"Column1.crs.type", "Column1.crs.properties"}),#"Expanded Column1.crs.properties" operties", {"name"}, {"Column1.crs.properties.name"}),#"Expanded Column1.features""Column1.features"), mn1.crs.properties",#"Expanded Column1.features1" Table.ExpandRecordColumn(#"Expanded "Column1.features.id", "Column1.features.geometry", features.geometry" es"},{"Column1.features.geometry.type", tedValues" , each Text.Combine(List.Transform( , Text.From), ","), typetext}),#"SplitColumnbyDelimiter" etry.coordinates.1", gedType" n1.features.geometry.coordinates.2", type es.properties" eatures.properties", {"objectid", "pcode", "iso3", "name", "name alt", "loc type","loc subtype", "createdate", "createby", "closedate", "updatedate", "updateby", "source", "unhcr lumn1.features.properties.name alt","Column1.features.properties.loc type","Column1.features.properties.loc umn1.features.properties.unhcr s.globalid"}),#"Filtered Rows" Table.SelectRows(#"Expanded Column1.features.properties", each true)in#"Filtered Rows"info@cartong.org www.cartong.orgPage 37 40

Power BI User GuideV. Publish dashboard from PowerBI desktop to PowerBI service1. Save the report as a new .pbix file. File Save As2. Select File Publish Publish to Power BI.For the first time, you will be asked to sign into your Power BI service account and selectthe destination on Power BI service (My workplace).3. Once the dashboard is published, you will be able to open it in Power BI service by clickingon the link.4. In Power Bi service interface, if you directly accessed the Report view, skip points 4 andotherwise select Workspaces My Workspace.5. Then, go to Reports section and click on your file name.info@cartong.org www.cartong.orgPage 38 40

Power BI User Guide6. In the Report view, find File Publish to web7. Once the dashboard is published to the web, you will be able to see the window with thelink to the dashboard which you can use in your email.8. To schedule an automatic refresh go to your Dataset, click on the “.” and select ScheduleRefresh.info@cartong.org www.cartong.orgPage 39 40

Power BI User Guide9. Turn to ‘On’, choose the frequency (hourly, daily or weekly depending on the Power BIversion you have) and the time of the refresh. Don’t forget to click Apply at the bottomto validate everything.info@cartong.org www.cartong.orgPage 40 40

Power BI Service is the online service accessed via PowerBI.com. 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.