The objective of the project was to consolidate DOE-417 data and refine it into a comprehensive report or dashboard.

Time Range : 2002 - 2023

This analytical tool aimed to uncover outage patterns, quantify their impact on communities, and identify potential grid vulnerabilities. Transparency was key, explicitly addressing data quality issues and any assumptions made due to missing or unreliable data.

The dataset included annual summaries in an Excel spreadsheet and supporting PDF documents. To streamline the dataset, I standardized time notations and divided dates into distinct columns. An intense focus on detail was crucial, rectifying inconsistencies like restoration preceding the event start time.Tools like Power BI and Excel were instrumental in visualizing insights. Line charts effectively highlighted the significant decrease in outage duration over time, while map charts offered a geographic perspective on changes. Heatmaps were used to identify outage frequency patterns, and box plots provided insight into restoration time distributions. Additionally, cards and slicers enhanced interactive exploration.

Steps Review:

• Carefully reviewed each sheet and clean unnecessary data in each sheets
• Combined 22 sheets
• Replace values noon to PM, evening to 6 PM, midnight to 12 AM, NA to 0, unknown to 0 and other annomalies
• Extracted states from area affected description and combined with ";" delimeter
• Then split extracted column to rows
• Affected customer divided each state proportionally
• Loss (MW) presented as XXX-XXXX, calculated average to replace. for example, 4000-8000 to 6000
• Millions replaced with number extracted multiplied by 1000000
• Categorized and sub categorized each type of disturbance. Total 30 sub category and 7 category

Ultimately, this project aimed to equip the U.S. Department of Energy with a powerful analytical tool, revealing insights critical for proactive grid management and strategic decision-making.