Profile Before Reporting
The file and column profiling sheets made the dataset easier to reason about before building visuals. This reduced guesswork and helped identify which fields were stable enough to use.
Overview
Project Summary
Public datasets are useful, but they rarely arrive in the exact shape needed for analysis. In this project, I worked through a NOAA weather dataset with multiple station files, inconsistent field availability, long historical date ranges, and many columns that were not needed for the final report.
The finished workbook focuses on a small set of selected stations and precipitation reporting. Behind that simple dashboard is a larger preparation workflow: identifying which files had usable columns, checking value ranges, filtering the dataset down to reporting fields, standardizing dates, and building pivot-based views for monthly, yearly, and semi-annual comparisons.
I built this as an Excel-centered project on purpose. Many businesses still run important reporting from Excel, and a strong Excel workflow can be extremely valuable when it is structured, documented, and supported by clean transformation logic.
Walkthrough
Video Overview
This project walkthrough shows the workbook structure, the Power Query workflow, and how the dashboard connects back to the prepared NOAA weather data.
Business Problem
What This Workflow Needed To Solve
The real challenge was not making a line chart. The challenge was getting from a folder of raw files to a reliable reporting table while keeping enough visibility into the process to trust the output. That is the kind of work that often sits between analytics, operations, and reporting.
- Which source files contain the fields needed for reporting?
- Which columns are consistent enough to keep?
- Are the date fields usable across a long historical range?
- Can station metadata such as latitude and longitude support map-based context?
- How can the raw dataset be reduced into a smaller reporting table without losing the important fields?
- What seasonal and long-term precipitation patterns are visible by station?
- Can the final workbook remain explainable to someone reviewing it later?
Data Profiling
Understanding The Raw Files Before Building The Report
Before building visuals, I profiled the files to understand their row counts, column counts, available fields, and basic value ranges. This step helped separate useful reporting fields from noise before the data reached the dashboard.
Power Query Workflow
Turning Many Files Into A Reporting Table
Power Query handled the repeatable transformation work: loading the source files, keeping only the files and columns needed, parsing CSV content, promoting headers, standardizing dates, adding calendar fields, and applying final data types.
This is one of the most important parts of the project because it shows the reporting layer was not assembled manually. The workbook has a repeatable cleanup path, which is what makes the final dashboard more useful than a one-off spreadsheet.
Prepared Dataset
A Smaller, Cleaner Dataset Built For Analysis
After the source files were reduced and transformed, the reporting table included the key station, date, location, and precipitation fields needed for the workbook.
Location Context
Adding Geographic Context To The Station Data
The station latitude and longitude fields were used to show where the selected weather stations are located. This gives the report more context than a table of station names alone.
Dashboard
Historical Weather Reporting Dashboard
The final dashboard brings together monthly patterns, long-term station trends, map context, and semi-annual comparison views into a single reporting page.
This view gives a reviewer several ways to view the data: seasonal behavior by month, long-term annual movement by station, station geography, and selected semi-annual comparisons.
Analysis Views
Pivot-Based Views Used To Build The Story
The supporting worksheet views show the analysis behind the dashboard. They are intermediate views which feed into the main report page and are used to validate it.
Design Decisions
Why The Workbook Was Structured This Way
Keep The Final Dataset Narrow
The reporting table was intentionally reduced to the fields needed for analysis during preprocessing. Station identity, date fields, location fields, and precipitation values were identified as stable fields across the specified reports imported by the dataset.
Use Excel Strengths
Pivot tables, slicers, timelines, charts, and maps were used where they made sense instead of forcing every step into a heavier BI platform.
Show The Workflow
The workbook includes visible preparation and analysis layers so the final dashboard is easier to audit, explain, and improve later.
Key Takeaways
What This Project Demonstrates
- Raw public datasets often need a profiling step before they are safe to use in a dashboard.
- Power Query can turn a folder-based data source into a repeatable reporting workflow instead of a process requiring extensive manual pre-transformation.
- Excel can still be a strong reporting tool when the workbook is structured around clean source data, pivot-based analysis, and clear dashboard sections.
- Location metadata added useful context and helped make the station comparison easier to understand.
- The project shows both technical cleanup work and business-facing reporting, which is the combination many real reporting projects require.
Tools Used
Technology Stack
Excel
Pivot tables, pivot charts, slicers, timelines, map visuals, workbook organization, and dashboard presentation.
Power Query
Folder ingestion, CSV parsing, column selection, date parsing, calendar fields, filtering, cleanup, and final data typing.
Data Profiling
File-level review, column availability checks, value range checks, and source structure analysis before dashboard development.
Outcome
Reporting Scope
The final workbook demonstrates a full Excel reporting workflow. This includes source file review, data profiling, Power Query transformation, prepared reporting tables, geographic context, pivot analysis, and dashboard design.
This kind of workflow is useful when a team needs more than a chart but does not necessarily need a full application. It creates a clear bridge between raw files and repeatable reporting, while keeping the logic visible enough for review and future improvement.
The project also shows the type of practical reporting work I focus on. Take messy data, make it understandable, and turn it into a dashboard or workbook that supports better analysis.