⚙️Data Cleaning & Transformation (Power BI or Excel
Power Query)
🧩 PHASE 1: Data Import & Cleaning
🪄 Step 1: Import Dataset
1. Open Power BI Desktop.
2. Click on Home → Get Data → Text/CSV.
3. Browse and select World_GDP_Large_Dataset.csv.
4. Click Transform Data (⚙️) instead of “Load” — to open Power Query Editor.
🧹 Step 2: Basic Cleaning in Power Query Editor
You’ll notice some data inconsistencies (added for student practice).
1. Use First Row as Headers
○ Click on “Use First Row as Headers.”
2. Check for Blank or Null values
○ Click on the column dropdown → Remove Empty Rows.
○ Example: Some countries might have missing GDP in early years.
3. Trim Extra Spaces
○ Select all text columns → Right-click → Transform → Trim.
4. Fix Inconsistent Country Names
○ Example: "United States " vs "United States" → use Replace Values to
clean.
5. Change Data Types
○ Year → Whole Number
○ GDP → Decimal Number
○ Latitude, Longitude → Decimal Number
6. Remove Unnecessary Columns
○ If any column is irrelevant (like “Note” or “Extra”), right-click → Remove
Columns.
7. Fill Missing GDP Values
○ Select GDP → Transform → Fill Down (or Replace nulls with 0 for simplicity).
8. Click Close & Apply.
🌍 3. Visualization Tasks (Step-by-Step)
(i) Symbol Map – GDP by Country
Goal: Show GDP on a map using latitude and longitude.
Steps:
1. From Visualizations, choose Map (Symbol Map).
2. Drag:
○ Latitude → Latitude field well
○ Longitude → Longitude field well
○ Country → Legend (Color Marks Pane)
○ GDP → Size/Label
💡 You’ll see circular symbols on each country showing GDP size.
(ii) Bar Graph – GDP of Belgium (2006–2026)
Steps:
1. Select Bar chart visualization.
2. Drag:
○ Year → X-axis
○ GDP → Y-axis
○ Country → Filters Pane → Select Belgium
3. Format → Data colors → Choose a consistent color (e.g., blue).
💡 Now you can compare Belgium’s GDP over time visually.
(iii) Pie Chart – GDP Comparison (India, Nepal, Romania, South Asia,
Singapore, Year 2010)
Steps:
1. Select Pie Chart visualization.
2. In Filters:
○ Year = 2010
○ Country → select only the 5 countries.
3. Drag:
○ Country → Legend
○ GDP → Values (Angle field)
💡 The chart will show the proportional GDP share of each country.
(iv) Compare Bhutan & Costa Rica (2016–2018)
Steps:
1. Use Clustered Column Chart.
2. Filters:
○ Country → Bhutan, Costa Rica
○ Year → 2016–2018
3. Drag:
○ Year → X-axis
○ GDP → Y-axis
○ Country → Legend
💡 You’ll see GDP growth across years for both countries.
(v) Scatter Plot – GDP of Mexico, Algeria, Fiji, Estonia (2004–2006)
Steps:
1. Choose Scatter Chart (or Bubble).
2. Filters:
○ Country → select 4 countries
○ Year → 2004–2006
3. Drag:
○ Year → X-axis
○ GDP → Y-axis
○ Country → Legend
○ GDP → Size (optional)
💡 Each circle shows GDP value per country per year.
🎨 4. Dashboard Design Tips
To make it beautiful and interactive:
● Use a light background color (like pale grey or blue).
● Add slicers for Year and Country on top.
● Group visuals using shapes or borders.
● Add a title: “🌎 Global GDP Analysis Dashboard”.
● Use consistent color schemes (e.g., cool blues or greens).
● Enable filters and tooltips for interactivity.