__STYLES__
In this project, we had event-level power outage data going back to 2002, but there were also significant issues with data quality and integrity. I combined and cleaned raw data and created a report to help the client understand patterns and trends related to outages.
Since the events were recorded over 10 years and the table formats changed several times, I grouped the tables with the same formats and first brought them to a common format. The data from 2003 to 2005 required the most effort. I completed the entire cleaning process in Power Query.
The time was indicated in the a.m. p.m. format, to change this I used conditional columns and added 12 hours to the time marked p.m. All values like "ongoing" i replased by null.
I cleaned the data on NERC Region using a conditional column, replacing obvious typos.
I replaced the data on power losses and customers affected manually. Where data ranges were indicated (for example, 700-1000), I replaced them with the average between the minimum and maximum. I replaced all approximate and inaccurate values with null. Thus I didn`t recognize 15 Demand Loss values and 14 Customers Affected values. Total number of missing values out of 3903 events is 1496 and 770 respectively. I didn't touch the 0 values because there is a big difference between null and 0.
I replaced Event Type values using a conditional column. And combined all weather-related causes into the category of natural disaster. "Other" values, as well as all that I could not recognize, I replaced with "Unknown". The total of these values is 210.
The most difficult task was to streamline the states :
This is a great example of real data that a data analysis engineer has to deal with. I am very glad that there was no additional requirement to create functions to automate this entire process for further application to new unknown data.