DEV Community

Cover image for 🌤️ Day 69 of My Data Analytics Journey
Ramya .C
Ramya .C

Posted on

🌤️ Day 69 of My Data Analytics Journey

#analytics #api #learning

Diving Into Live Weather Data with APIs, JSON & Power Query

Today is Day 69 of my Data Analytics journey, and it turned out to be one of the most hands-on learning days so far. I officially started my Weather Data Project, which focuses on collecting real-time weather information, transforming it, and preparing it for future dashboards and analytics.

This project is important for me because it combines multiple skills I’ve been learning:
✔ Working with APIs
✔ Understanding JSON structures
✔ Loading data into Excel
✔ Using Power Query for transformations
✔ Documenting data for analysis

This is the type of real-world workflow data analysts perform daily — so it feels exciting to build something end-to-end.

☁️ Project Overview

The goal of this project is simple:

Connect to a live weather API → Load the JSON data into Excel → Clean, transform, and analyze it.

By the end of the project, I want to build:

  • an automated Excel dashboard (or Power BI dashboard later),
  • visualizations showing temperature, humidity, and climate patterns,
  • and possibly a refreshable daily weather log.

🔧 Step-by-Step Progress Today

1. Identifying a Live Weather API

I started by choosing a weather service that offers:

  • real-time data,
  • free API key access,
  • and JSON response format.

This allowed me to work with a real, constantly changing dataset — perfect for learning.

2. Signing In & Generating My API Key

After registering, I received my personal API key.
This key allows me to:

  • access authenticated endpoints
  • request live weather information
  • customize parameters (city, units, format, etc.)

I stored it safely because API keys should always remain private.

3. Exploring & Understanding the JSON Data

Before loading data anywhere, I copied the API URL into a browser and examined the raw JSON.

This helped me understand:

  • what fields were available,
  • how nested the structure was,
  • and which fields mattered (temperature, humidity, windspeed, etc.)

This step is crucial because JSON can often contain many nested objects and arrays.

4. Loading JSON Data Into Excel Using Power Query

My main tool today was Power Query, which is amazing for data extraction and transformation.

The process I followed:

  1. Go to Data → From Web
  2. Paste the API URL with the key
  3. Power Query identifies the JSON structure
  4. I expanded fields into table columns
  5. Converted data types
  6. Cleaned unnecessary fields

❗ Issue I Faced (Important Learning Moment)

The first time I tried loading the JSON:

  • Excel refused to open the data
  • The query wouldn’t load
  • I kept getting a blank or error output

After researching, I realized my local Excel version needed the Power Query add-in installed.

So I:

  • Downloaded the standalone Power Query
  • Signed into my Microsoft account
  • Restarted Excel
  • Reconnected to the API

And finally… it worked! 🎉

This was a good reminder that real-world data work also involves environment setup, plugin issues, version compatibility, and troubleshooting.

🧹 5. Data Transformation & Additional Columns

Once the data loaded successfully:

  • I added new columns (like Celsius → Fahrenheit conversion)
  • Set correct data types
  • Added a "Description" column for metadata
  • Documented what each field means
  • Cleaned the structure to prepare for analysis

This process helped me understand how important proper documentation is for future dashboards.

🚀 What’s Coming Next?

For the next few days, my plan is to:

🔜 1. Build a clean transformed dataset

Remove unnecessary fields, rename columns, and standardize types.

🔜 2. Create visualizations

  • Temperature trend charts
  • Humidity patterns
  • Wind speed comparison
  • Daily weather summary

🔜 3. Automate the data refresh

So Excel updates the data whenever I click Refresh.

🔜 4. Write clean documentation

So the workflow is clear and reproducible.

Eventually, I may move this project into Power BI to create a fully interactive weather dashboard.

🌱 Reflections

Day 69 taught me a valuable lesson:

Learning data analytics isn’t just about tools — it’s also about problem-solving, debugging, and understanding how data flows from source to analysis.

I’m proud of today’s progress and excited to keep building!

🏷 Tags

#DataAnalytics #API #JSON #PowerQuery #Excel
#Day69 #LearningInPublic #WeatherData
#WomenInTech #RamyaAnalyticsJourney

Top comments (0)