Master Data Cleaning: Python, Excel & Power Query

Master practical data cleaning skills with Excel formulas, Power Query automation, and Python scripts.

What you'll learn

Identify and fix messy, inconsistent, incomplete, and duplicate data across various sources.

Use functions like IF(), TRIM(), TEXT(), VLOOKUP(), and DATA VALIDATION to clean and format spreadsheets effectively.

Import messy files, transform column headers, remove blanks, filter rows, and automate repetitive cleaning using Power Query.

Use Python’s pandas library to load, clean, merge, and transform real-world datasets using functions like .dropna(), .fillna(), .str.replace(), and .groupby().

Standardize inconsistent date formats, correct column naming issues, and unify naming conventions using both Excel and code.

Identify and remove exact and near-duplicate records using Excel tools, Power Query logic, and pandas .duplicated() methods.

Build workflows that allow you to clean new data in one click using Power Query and reusable Python scripts.

Requirements

Ability to navigate files, folders, and applications confidently.

Basic understanding of Excel spreadsheets

Experience with formulas like SUM, IF, and VLOOKUP (helpful but not required)

Understanding of basic Python syntax (print(), variables, lists/dictionaries)

No advanced coding required — Python basics will be reinforced in the course

Willingness to try tools like Power Query and Python/pandas even if you're new to them.

Power Query is built-in from Excel 2016+

Python (v3.7 or later) Installed via Anaconda or Python org

Jupyter makes it easy to follow step-by-step

VS Code or any other IDE is also fine

Internet Connection Required to download data files, Python packages, or follow along with GitHub-hosted resources.

Description

Master Data Cleaning: Python, Excel & Power QueryThe Complete Guide to Cleaning, Transforming, and Preparing Real-World Datasets for AnalysisAre you tired of spending hours cleaning messy spreadsheets or trying to make sense of inconsistent data? Do you want to master the essential data wrangling skills that professionals use every day to turn chaotic raw data into clean, structured datasets ready for analysis?You’ve found the right course.Whether you're a beginner, a data enthusiast, or a working professional looking to improve your data handling skills, this course will teach you how to clean and transform real-world data using Microsoft Excel, Power Query, and Python (pandas) — all with hands-on projects and real business scenarios.What This Course Teaches YouData cleaning is not glamorous, but it's one of the most critical steps in the data lifecycle. Without clean data, your dashboards, reports, and machine learning models will all suffer.This course helps you develop a toolbox of techniques to clean, validate, merge, and prepare datasets — no matter where the data comes from.By the end of the course, you’ll confidently handle:Duplicate entriesInconsistent text formatsMissing or blank fieldsMerging messy datasetsReconciliations (like sales vs. inventory)Transforming dirty Excel or CSV files into clean data tablesWe’ll show you how to solve these problems using:Excel formulas like IF(), TRIM(), TEXT(), and VLOOKUP()Power Query for automated and repeatable transformationsPython’s pandas library for efficient, code-based data cleaning Tools CoveredI focus on the three most widely used data cleaning tools:Microsoft ExcelGreat for ad-hoc cleaning and understanding patterns quickly. You’ll learn how to:Use formulas for detecting and fixing issuesApply data validationUse PivotTables for quick aggregationsPower Query (Excel/Power BI)Ideal for automating the cleanup process. You'll learn to:Import and transform messy filesNormalize headers, split/merge columnsRemove blanks and errors with one-click transformationsPython (pandas)The industry-standard tool for scalable data cleaning. You’ll learn:How to load and inspect messy datasetsUse dropna(), fillna(), replace(), str.lower(), and moreMerge datasets and handle duplicates with easeNo prior coding experience is required — we guide you step-by-step. Who This Course Is ForThis course is designed for a wide range of learners, including:Data analysts and business analysts who want to clean data faster and more reliably.Students and career switchers who want to build a data portfolio and gain practical skills.Excel users and non-programmers looking to upgrade to more powerful tools like Power Query and pandas.Consultants and freelancers who deal with messy client data and need repeatable workflows.Professionals in operations, finance, HR, sales, and marketing who deal with spreadsheets and CSVs every day. What You’ll Learn (By Section)Data Cleaning BasicsWhat makes data messyCommon formats, missing values, and inconsistenciesExcel for Data CleaningCleaning with formulas: IF, TEXT, VLOOKUP, TRIMUsing filters, validation, and conditional formattingPower Query for AutomationLoading data from folders and filesSplitting, merging, and unpivoting columnsRemoving duplicates and fixing typesPython & pandas for Real-World CleaningCleaning text columnsRemoving duplicatesDealing with nulls and formatting issuesMerging datasets with .merge() and .concat()Capstone ProjectsClean messy HR datasets with inconsistent employee names and IDsReconcile sales vs. inventory using merges, grouping, and filtersTransform multiple Excel files into a unified clean datasetEach project mimics a real job task you’ll face in the field — perfect for practice and your portfolio. What You’ll AchieveBy the end of the course, you'll be able to:Confidently clean and prepare messy Excel/CSV data using industry toolsAutomate data cleaning workflows using Power Query and pandasMerge multiple datasets with inconsistent IDs or formattingSpot and resolve real-world issues like duplicates, bad formatting, and null valuesBuild reusable scripts and queries for repeatable processesPresent clean, analysis-ready data for reporting or machine learning What’s IncludedStep-by-step lessons with examples4+ real-world datasets to practice onWhy This Course Is DifferentThis isn’t just theory — it’s hands-on learning from the ground up. I don’t just show you tools; we show you how to use them in the messy, imperfect world of real business data.Each section ends with practical challenges and mini-projects to reinforce your skills. You’ll walk away not just knowing what to do, but why it works. Ready to Master Data Cleaning?Whether you're building dashboards, preparing reports, or feeding a data pipeline — clean data is your foundation.Enroll now and start cleaning smarter — not harder.Learn Excel, Power Query, and Python the practical way.

Overview

Section 1: Introduction to the Data Wrangler Course

Lecture 1 Become a Data Wrangler - Introduction

Lecture 2 About The Author

Lecture 3 Why Data Wrangling Matters?

Lecture 4 The Tools You’ll Learn in This Course

Lecture 5 Data Wrangling - Course Introduction

Section 2: What is Data Wrangling?

Lecture 6 What is Data Wrangling?

Lecture 7 The Data Wrangling Lifecycle

Lecture 8 Where Does Wrangling Fit in Data Analytics?

Lecture 9 Data Wrangling vs. Data Analysis

Section 3: Real-World Examples of Messy Data

Lecture 10 Real-World Examples of Messy Data

Section 4: The Tools You need for Wrangling

Lecture 11 The Tools You’ll Learn in This Course

Lecture 12 Python - Pandas for Data Wrangling

Lecture 13 Data Cleaning using Pandas - Example

Lecture 14 Data Cleaning Using Excel - With Example

Lecture 15 Data Cleaning Using Power Query - With Example

Section 5: Data Cleaning in Excel - Detail

Lecture 16 Data Cleaning in Excel - Detail

Section 6: Data Cleaning in Power Query

Lecture 17 Data Cleaning in Power Query

Section 7: Data Cleaning Using Pandas - Details

Lecture 18 Data Cleaning Using Pandas - Details

Section 8: Hands on Lab Exercise - Data Wrangling - Pandas

Section 9: Export, Share and Automate Wrangling Output

Lecture 19 Export, Share and Automate Wrangling Output

Aspiring Data Analysts & Data Scientists Looking to build a strong foundation in data cleaning — the most critical and time-consuming step in any data project.,Business Analysts & Excel Users Who want to go beyond spreadsheets and learn how to automate and scale data cleaning using Power Query and Python.,Students & Recent Graduates Preparing for roles in data, analytics, or reporting and looking to gain practical, hands-on skills with real-world datasets.,Professionals Working with Messy Data In finance, HR, sales, marketing, or operations who regularly receive inconsistent or incomplete data from different sources.,Anyone Transitioning to a Data Role Who may not have a programming background but wants to quickly upskill in essential data wrangling techniques using modern tools.,Freelancers and Consultants Who clean and combine data from multiple clients and need reliable, repeatable workflows.