Power BI Retail ETL Project Part 1: Importing and Combining the Online Retail II Dataset

🚀 Retail ETL Pipeline Series – Part 1

Power BI ETL Project: Importing and Combining the Online Retail II Dataset

One Dataset. Seven ETL Journeys. Endless Possibilities.

Welcome to Part 1 of the Retail ETL Pipeline Series.

In this series, we will transform a real-world retail dataset into a complete Data Engineering portfolio using multiple architectures and technologies.

The journey starts with the simplest and most beginner-friendly approach:

📊 Power BI Only ETL

🌍 Why This Dataset?

Many beginners spend months learning Power BI, SQL, Python, and Cloud technologies but never get the chance to work on a realistic business dataset.

That changes today.

We are using the famous Online Retail II Dataset, a real e-commerce dataset containing more than one million transaction records collected over two years.

  • 🛒 Real Retail Transactions
  • 📈 Revenue Analysis
  • 👥 Customer Analytics
  • 🌎 Multiple Countries
  • 📅 Multiple Years
  • 💰 Business Intelligence Use Cases

This is exactly the kind of data that helps learners move from tutorials to real projects.

🎯 The Goal of Today’s ETL

Before creating dashboards, KPIs, and analytics, we need a clean and structured dataset.

Our objective is simple:

  • Import both Excel sheets
  • Combine the data
  • Prepare the staging layer
  • Create a clean foundation for future transformations

📂 Step 1 – Load the Dataset into Power BI

Open Power BI Desktop and import the Online Retail II Excel file.

Power BI will display two worksheets:

  • 📅 Year 2009–2010
  • 📅 Year 2010–2011

Many beginners ask:

🤔 Should We Select One Sheet or Both?

✅ Select BOTH sheets.

The dataset is split across two years, and our goal is to build a complete retail analytics project.

If we only load one sheet, we lose a significant portion of the business history.

📸 Power BI Navigator Screen

Insert your screenshot below this section:

📷 Insert Power BI Navigator Screenshot Here
(Year 2009–2010 and Year 2010–2011 selected)

⚡ Important Decision: Load or Transform?

When the Navigator screen appears, Power BI provides two options:

  • Load
  • Transform Data

For a professional ETL workflow:

✅ Choose "Transform Data"

We are building a Data Engineering project, not just importing data.

All cleaning and transformations should happen inside Power Query.

🏗 Building a Real Data Engineering Structure

One of the biggest mistakes beginners make is creating everything in a single query.

Professional Data Engineering follows layers.

We will use the same structure across Power BI, PostgreSQL, BigQuery, dbt and Airflow projects.

This layered architecture makes projects easier to maintain, easier to debug and more aligned with industry practices.

🧹 What Will We Clean Next?

After combining the two sheets, we will begin cleaning the data.

Some common issues include:

  • ❌ Missing Customer IDs
  • ❌ Cancelled Orders
  • ❌ Invalid Quantities
  • ❌ Invalid Prices
  • ❌ Duplicate Records
  • ❌ Incorrect Data Types

Each step will be documented so learners can understand not only what we are doing but also why we are doing it.

💡 Why DETLENG Uses One Dataset Multiple Times

Most learners collect datasets.

We build solutions.

At DETLENG, the focus is not on touching hundreds of datasets.

The focus is on mastering architecture, ETL thinking and business problem solving.

🚀 One Dataset. Seven Architectures.

  1. Power BI ETL
  2. BigQuery + Power BI
  3. BigQuery + Looker Studio
  4. Python + PostgreSQL + Power BI
  5. Python + BigQuery + Power BI
  6. dbt + BigQuery + Looker Studio
  7. Airflow + BigQuery + dbt + Power BI

🎓 Final Thoughts

Today’s lesson may look simple:

Import an Excel file. Select two sheets. Open Power Query.

But this is where every professional ETL pipeline begins.

Every dashboard, warehouse, cloud platform, and analytics solution starts with a clean and well-structured dataset.

Take your time. Follow along. Build the project yourself. Break things. Fix things. Learn by doing.

❤️ Build. Learn. Engineer. Grow.

This is the DETLENG way.

Practical Learning • Real Projects • Real Skills

🌐 DETLENG.COM

Popular posts from this blog

Production ETL Pipeline Execution Overview

Image
🚀 Building a Production-Style ETL / ELT Pipeline on Google Cloud A practical engineering walkthrough of designing modern data pipelines using BigQuery, SQL transformations, incremental loading, and production-oriented cloud architecture. Recently, I explored how modern ETL / ELT workflows are designed professionally inside real-world analytics environments using Google Cloud technologies. Instead of focusing only on theoretical concepts, the goal was to understand how raw operational data becomes clean, analytics-ready business data through structured engineering practices. 📘 ETL / ELT Pipeline Overview The pipeline was designed to simulate a production-style analytics workflow where raw business data flows through multiple engineering layers before becoming usable for reporting and decision-making. Dataset / API ↓ Python Extraction Script ↓ Ra...
Read more

Choosing the Right Dataset for a Realistic ETL / ELT Pipeline Project

Image
📊 Choosing the Right Dataset for a Realistic ETL / ELT Pipeline Project Understanding why dataset selection matters in modern cloud data engineering workflows. While exploring Google Cloud Data Engineering workflows, one thing became very clear: a strong ETL / ELT project does not begin with SQL queries or dashboards. It starts with choosing the right dataset. For practical engineering simulations, portfolio development, and production-style pipeline learning, an E-commerce / Online Retail dataset proved to be one of the most effective choices because it naturally contains the kinds of operational problems real data engineers solve daily. 🛒 Example Dataset Structure Typical business columns explored inside the retail dataset included: order_id customer_id product_id category quantity price payment_method order_status city order_timestamp This structure allows realistic simulation of modern engineering workflows used inside analytics syste...
Read more

🔍 Power BI Retail ETL Part 4 | Investigating Data Quality Issues in Online Retail Data

Image
🚀 Power BI Retail ETL Project Part 4 🔍 Investigating Real-World Data Quality Issues in the Online Retail II Dataset Welcome back to the Retail ETL Pipeline Series . In Part 1, we imported the Online Retail II dataset into Power BI. In Part 2, we combined both yearly datasets into a unified staging layer. In Part 3, we performed data profiling to understand the overall health of the dataset. Now comes one of the most important responsibilities of every Data Engineer: 💡 Data Quality Assessment Before building dashboards, KPIs, reports, or business analytics, we must understand the quality of the data itself. Professional Data Engineers never assume data is correct. They investigate. They validate. They challenge assumptions. That is exactly what we did in this phase of the project. 📊 What We Discovered During Data Profiling After combining both years of retail data, our staging layer contained: ✅ More than 1 Million Transaction Records ✅ Multipl...
Read more