Wanli's Data Story

From Raw Data to Business Insights: A Full Dashboard Project

This project is part of my personal data analytics portfolio. Using open-source data and Power BI, I built a complete dashboard that uncovers trends in bike usage patterns, member vs casual user behavior, and resource utilization. This article outlines my end-to-end process — from data acquisition and cleaning to visualization and analysis.

Data Source

The dataset comes from Divvy Bike Sharing, which offers monthly trip data from Chicago’s shared bike system. Each row represents a unique ride, with the dataset including:

• Trip start and end times
• Station names and locations
• Type of bike used (classic, electric, or docked)
• Rider type (member or casual)

For this project, I used data from January 2021 (202101-divvy-tripdata.csv).

Understanding the Data Structure

Each record in the dataset contains:

• ride_id :Unique ride identifier
• rideable_type: Type of bike used
• started_at: Ride start timestamp
• ended_at: Ride end timestamp
• start_station_name: Starting location
• end_station_name: Ending location
• member_casual: Rider type (member or casual)

ETL in Power BI

1. Data Import

• Imported the CSV file into Power BI Desktop.
• Ensured datetime columns were properly recognized for time-based analysis.

2. Data Cleaning

• Removed duplicates and null values.
• Extracted new columns:
  • Date only from started_at
  • Hour of the ride
  • Day of week
  • Month (YYYY-MM format)

3. Feature Engineering

I created several calculated columns and measures using Power Query and DAX:

• duration_mins: Difference between ended_at and started_at in minutes
• OrderHour: Hour of ride start (0–23)
• OrderWeekday: Day of the week
• OrderMonth: Month for time trend analysis
• MostActiveBikeType: Type with the highest ride count

Dashboard Visualizations

I designed multiple interactive pages to explore and present the data:

Daily Ride Trend

A line chart showing daily ride volumes in January 2021. Clear dips and peaks reflect weekday vs weekend usage patterns.

Monthly Trends (optional enhancement)

Suggested expansion: A month-over-month line chart to show long-term usage trends.

Weekly Ride Peaks

A bar chart comparing ride volumes by weekday. Friday and Saturday emerged as the most active days, indicating both commuting and leisure use.

Ride Distribution by Hour

This chart reveals time-of-day patterns — with clear morning and evening peak hours, reflecting commuter behavior.

Bike Type Popularity

A donut chart showing that classic bikes accounted for over 63% of rides, making them the most commonly used type.

Member vs Casual Users

• Pie chart: shows members make up ~81% of total rides.
• Behavioral contrast:
  • Casual users are more active on weekends and midday.
  • Members ride more evenly across days and hours.

Ride Location

Used start_lat and start_lng for mapping popular stations or ride clusters on Map.

Key Insights

1. Clear peak usage times at 8 AM and 5 PM, aligning with workday commutes.
2. Friday and Saturday are the busiest days, highlighting both work and recreational use.
3. Classic bikes dominate the system, while docked bikes are rarely used.
4. Members ride more consistently and frequently than casual users.
5. Median ride time is 9 minutes, showing most trips are short and likely for commuting.

Dashboard Highlights

• Built fully in Power BI, from import to visuals
• Used both Power Query (for ETL) and DAX (for advanced KPIs)
• Designed for clarity, insight, and business value
• Easily extendable to multiple months or different cities

Project Deliverables

• Full interactive Power BI dashboard
• Insight summary with visual storytelling
• Future roadmap: interactive maps and calendar heatmaps
• Portfolio-ready report showing data-to-insight workflow

Tool Stack

• Power BI : Dashboarding and ETL
• Power Query: Data transformation
• DAX: Measures and calculated fields
• CSV Files: Raw input data