Thulehem

Declaration: this intern project has been given the consent by Thulehem for the publication of identifiable details, which can include photo, name, data and visualizations.

 

COMPANY DESCRIPTION

Thulehem was built in 1962 and is an active senior living with 270 apartments and with an individual service building containing a restaurant, clinic, club, library and gym etc.

Thulehem have maintained the buildings for many years and done some improvements but their new outspoken strategy is to develop the property and find new ways to offer services and to reduce costs related to electricity/heating and water.

 

PERPARE PHASE

Business task

Creating a new framework for raw data, makes the data more structured and more analysis-ready, then visualize data in Power BI for stakeholders to easily follow up the electricity consumption and identify the trends.

Visualization task

Create a report with dynamic visuals that demonstrate the elctricity consumption with focusing on

  • Each building
  • Month
  • Year
  • Map
  • The percentage change comparison between one specific month and average monthly consumption for 2014-2021.

 

Stakeholders

  1. Boardmembers – Including different experts with different professionals like law, human relationships etc.
  2. Maria – CEO of thulehem
  3. Cristoffer – technical development manager

Data

The data used was internally provided. It contains monthly electricity consumption data from 2014 – 2021 and a series of calculation data like summation, average and percentage rate. Unfortunately they are not organized in an easily identifiable manner for SQL and Power BI.

Dataset name: Energiuppföljning Thulehem.xlsx

 

PROCESS PHRASE

Recreate datasets

Efter several communications with Cristoffer, tech-development manager, about his demands on  visualizations, I created three new datasets based on the original dataset Energiuppföljning Thulehem.xlsx, and re-designed the structure for each dataset to make them identible for SQL or Power BI.

  1. EL_consumption – Monthly EL consumption, 2014-2021 for each properties
  2. Building_location – Add laitutide and longitude for later map visualization
  3. sortorder – To visualize buildings by its natural order, I add the order column in the number data type, as the building number column (ex: 1-6, 7-11) is in text data type.

 

Clean the datasets in Excel

  • Checked for duplicate data using the ‘duplicate data’ tool in Excel
  • Formatted date data into DD/MM/YY date format
  • Formatted all numerical data into Number format
  • Separated laitutide and longitude into two columns for later analysis

 

POWER BI PHASE

Transform data

Step 1: Cleaning the data

  • Promoted the headers
  • Rechecked the data type for each column in each table and corrected them if needed
  • Extracted separately from DD/MM/YY date column to get individual month, month name and year columns for later analysis

Step 2: calculate average monthly values

  • I duplicated the EL_consumption table. In that table, I used the group by function to get each monthly average electricity consumption per building in 8 years (2014-2021), the column was named as Avg, in total 192 values (12 months * 16 buildings). Thulehem asked to have this as a key figure because they have a new strategy to invest from now on to reduce their electricity consumption and therefore wanted to see the result from 2021 and forward compared to previous years. *(To ensure the accuracy of values, i used SQL to calculate again, and got the same result.)

Step 3: Merge the queries

  • Using building nr column as key column, merged sortorder table and EL_consumption table, and expanded the sort order column into EL_consumption table.

  • As duplicated_el table and EL_consumption table did not have same unique key column for merging, so I created that key column, “adressmonth” by custom column function, then merged these two tables together and expanded the Avg column to EL_consumption table.

  • Adding percentage column by custom column function

Step 4: Sort rows ascending

  • As the report page doesnt support multiple sortings, so i sorted here instead.

 

Data modeling

As shown in the picture below, I connected sortorder, duplicated_el, location tables and EL_consumption table together, based on their own unique key column in between.

 

Create report with visuals

I used bar chart and line chart to easily see the monthly and yearly electricity consumption pattern, for exemple in 2020, electricity had dropped to the lowest  and address 41 Vipegården has always consumed the most for each month in the year.

Map visuals has been used here as well to help the boardmembers with different backgrounds to better connect the statics to the actual building.

Table visual shows an overall list of the month consumption and compared it with the average consumption for 2014-2021. The result is showed in percent.

Lastly i added three slicer visuals for year, month and building number on the report canvas for stakeholder or analysts to easier filter out unneeded column and focus on the important data tables.