I will be playing the role of a senior Analyst Consultant hired by the U.S. Department of Energy (DOE).
Electricity outages are a growing concern as we enter an age of unprecedented energy demand and climate disasters.
We have event-level power outage data going back to 2002 but have struggled to make sense of it due to severe issues with the data quality and integrity.
This is where you come in.
We need you to consolidate and clean up the raw data, and create a dashboard or report to help us understand patterns and trends around outages, quantify their impact on our communities, and identify possible weak points in the grid.
Last but not least, please explicitly call out any caveats or assumptions you make in regard to data quality issues or missing values.
Which are the most affected areas?
How many customers are affected?
What was the Demand loss due to power Outages?
Data Cleaning Process
This data needed a lot of cleaning.
I used Excel to clean the data. I don't know if there was an easier way to clean the data.
I wanted to separate all years and put them in one folder.
I started by cleaning each column in each year then transferring it to a new work book, then saved it as per year.
I did this for all years from 2016 to 2023.
For the unknown values in the demand loss and customers affected columns I replaced them with 0.
For the blank cells in the region column, I replaced them with Unknown regions.
I got the minutes and days columns in Power BI in the power query using the start dates and end dates that I created.
I styled my background in PowerPoint for each page. I got the images and icons from Google.
I used blank buttons to navigate from one page to another.
The home page consists of four buttons at the top which are Trends, Causes, Region, and Days.
There are four main KPIs which are the total customers affected, total demand loss, total outages, total regions, and total blackout days.
At the bottom, there is a Year slicer from the year 2016 to 2023. I also added year-over-year changes for the customers affected, demand loss, and outages.
There are 5 charts, a slicer, and four buttons.
The column charts were used to show the current trends for the selected year and the previous year. That is for the column chart for the total days with power outages against the months for the selected year, there is also a line chart for the previous year's total outages for the same months.
Causes Page There were three donut charts and a table used for visualization. The donut charts, it was filtered to only the top 3 causes of power outages. This was done to avoid clusters in the charts.
The table was incorporated since there was no visual that looked good. It was very clustered.
From the consumers' perspective, I would recommend that they invest in other sources of power such as solar energy, or purchase generators for the most affected areas. From the supplier’s perspective, I would recommend they work on improving or stopping the other causes of power outages other than Weather.