last updated: 27 March 2021 by Raphael (rapp@env.dtu.dk)
Follow these simple steps to visualize the results from the WHAT-IF model using power BI. This guide assumes you have the WHAT-IF model and know how to run it. If you wish to compare only some key indicators of different scenarios, look at Creating, running and comparing scenarios which is simpler.

• Install power BI desktop
  https://powerbi.microsoft.com/en-us/downloads/
• Get the WHATIF_visualize.pbix file which is the template file for WHAT-IF results – it is in the main model folder if you downloaded the example branch

• Run the WHAT-IF model for the desired scenarios using WHATIF_main.py, WHATIF_scenario.py or WHATIF_scenario_mpc.py, the output will be found in the Results folder TIP: by default the results will be in a folder named after the day, date and time of the model run, e.g. Mon28_10_2019_11h32

• You need to make sure you export the files necessary for powerBI, by choosing EXPORT = 'all' in WHATIF_scenario.py, WHATIF_main.py, or WHATIF_scenario_mpc.py

• The output of the model runs should then contain the following:

Mapping contains the links in the model (e.g. between reservoirs and catchments, markets and countries, crops and cultures …).
By default, there will be 4 outputs by scenario (in WHATIF_main.py, the default ScenarioName is WHATIF_main):
ScenarioName.txt (selected results as a python dictionary)
ScenarioName.xlsx (detailed results in an excel file)
ScenarioName_Balances.txt (Water, Energy, Food and Economic balances as a python dictionary)
ScenarioName_DV.txt (contains all the decision variables as a python dictionary)

• Define which scenarios to aggregate in the Scenarios_to_compare.xlsx file which should be located in the main folder of the WHAT-IF model.

In the _nscen _column put the names of the scenarios you want to see in your results and in the _refscen _column the scenario it should be compared to. The scenarios do not need to come from a same model run – but you need to put all scenarios you want to compare in a same folder. Warning: be aware of Mapping parameters – they need to be the same or contained – e.g. a scenario can have more reservoirs than another – but if a same reservoir is at different location between two scenarios this won't work for power BI

• Run WHATIF_scenario_to_powerBI.py Within the script, they are two options you need to define:

SHEET='YourExcelSheetName' where scenarios are listed within Scenarios_to_compare.xslx (you can so define different lists of scenarios in different sheets and activate the one you want) FOLDERNAME='NameFolderResults' within the Results folder of WHAT-IF (e.g. Mon28_10_2019_11h32) DIFFMODE=0 or 1 (For power BI, 0: use absolute values, 1: use relative value between nscen and refscen) SCENFILE='Scenarios_to_compare.xslx' by default (no need to change) CSVSEPARATOR = separator in CSV files, needs to match your powerBI settings CSVDECIMAL = decimal in CSV files, needs to match your powerBI settings. You can customize regional settings in powerBI: file-options and settings – options – regional settings. This script will produce the following outputs in your result folder: Balances used for power BI DecisionVariables used for power BI

You can use the output from previous steps to create your own template, or you can use the existing one by following these steps:

• 3.1 open the WHATIF_visualize.pbix file with power BI (the location of the file does not matter)
• In Edit Queries – Edit Parameters define the path to results and shapefiles

FolderPath = FULL PATH (not relative) to your Results folder (e.g. YourWhatifFolder\Results\ Mon28_10_2019_11h32) ShapeFilePath = FULL PATH to your shapefiles (by default in YourWhatifFolder\Data\Shapefiles) For now: you need to end your path by a \ (backslash) You NEED to have shapefiles for power BI to load your results– see Appendix A: How to generate a shapefile or a list of X,Y coordinates - or use the default ones form the Zambezi Study case (they will not work – but you will be able to see your non-shapefile dependent results = most of them)

• WE MADE IT !!! You can now look through your results by using power BI's interactive features If you want to load other results (by changing the path as in step 3.2) you might see blank results – this is because power BI has still previous scenarios selected – you just need to reset the scenario slicers. You can create your own graphs in addition to power BI template – BE AWARE OF UNITS! - – BE AWARE OF UNITS! - – BE AWARE OF UNITS! - – BE AWARE OF UNITS! - – BE AWARE OF UNITS! - – BE AWARE OF UNITS! - - you got it – BE AWARE OF UNITS! - !!!!!!!!! look at Appendix B: Use yearly/scenario/category average in power BI The default WHATIF_visualize template should be unit consistent – however it might be that when you look at multiple scenarios you might sum the values instead of averaging. If units are not stated and you need to assume the standard units of WHAT-IF (M$, Mm³, kt, 1000 ha, GWh, MW, normally per year or per month)

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map https://datanrg.blogspot.com/2018/12/using-wgs-84-shape-maps-in-power-bi.html Activate shape map in the options of power BI (tutorial 1) In QGIS: Create a copy of your shapefile using system coordinate WGS 84 (ESPG:4326) Convert to (Topo)json file in https://mapshaper.org/ Replace Countries_WGS84.json and Catchments_WGS84.json in Data/Shapefiles by your countries and catchments shapefiles. Replace ReservoirsBI.xslx and HydropowersBI.xslx by your list of reservoirs and hydropower plants, X and Y coordinates can be generated with QGIS – make sur projection is WGS 84 (ESPG:4326)

From the Data containing different scenarios, different spaces and different time steps (or more indices), one thing one has to be careful about is what is the value that will be shown. Sum, average? This can be chosen when dragging the data in a graph. However, one might want to do a mix. A pure sum will sum up all time steps, all scenarios, all spaces. Except if it's a graph with time on the axis, Usually we want to have the time (e.g. yearly) average, in this case you can: -"Create a new quick measure" by right clicking the data -select "Average per Category" in "Calculation" -use "Sum of …" in "Base value" -choose the time index in the "Category"

When you import data to a power BI doc, the path to the data power BI is an absolute path, hence if you transfer the power BI doc and the data, if the other person/computer tries to refresh the data, it will fail (as it will look for that path). The solution is to : 1 create a parameter that will be the path to the directory containing the data (how to create a parameter: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/) 2 In "File", "Options and settings", "Data source settings", update the path to data by using the parameter you defined in step 1 – hence the new user/computer, just needs to update the path to data in the parameter ("Edit queries", "Edit parameter") instead of updating every data file.