Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Power Query in Excel

Learn how to import, clean, transform, combine, and refresh data using Excel Power Query.

1. What Is Power Query?

Power Query is a data connection and transformation tool available in supported versions of Excel. It helps users import data from different sources, apply repeatable cleaning steps, combine datasets, and load the prepared results into a worksheet or data model.

Power Query is useful when you need to:

  • Import data from Excel workbooks, CSV files, and other supported sources.
  • Remove unnecessary columns and rows.
  • Correct data types and clean text.
  • Combine data from multiple files or tables.
  • Repeat the same transformations when source data changes.
  • Prepare clean data for PivotTables and reports.
Remember: Power Query records transformation steps. When the query is refreshed, Excel can reapply those steps to the updated source data, subject to the source connection and query settings.

2. Power Query Workflow

Image 1 — Import, Transform, Load
1. Source Excel / CSV Text / Database Raw data 2. Transform Filter rows Clean columns Set data types 3. Load Worksheet Data Model Prepared data 4. Analyze PivotTables Charts Reports Repeatable Data Preparation Saved query steps can be applied again during refresh. Always check the source, credentials, and refresh results.

Custom illustration of the typical Power Query workflow.

3. How to Open Power Query

  1. Open Microsoft Excel.
  2. Select the Data tab.
  3. Find the Get Data or data import options.
  4. Select a supported source, such as From Text/CSV or From Workbook.
  5. Select the file or source you want to import.
  6. Choose Transform Data to open the Power Query Editor.
Note: Menu labels and available connectors vary by Excel version and platform. Use the import options available in your installation.

4. Sample Data for Power Query

Create a CSV file named StudentSales.csv with the following sample data. We will use it to practice common transformations.

StudentSales.csv
StudentID,StudentName,Course,City,FeesPaid
101, Samadhan ,Java,Pune,45000
102,Samiksha,SQL,Mumbai,60000
103,Pradnya,Python,Pune,32000
104,Omkar,C#,Mumbai,75000
105,Neha,Java,Pune,28000
106,Samiksha,SQL,Mumbai,60000

Notice that the first student's name contains extra spaces, and one record is repeated. These are intentional examples for practicing data cleaning.

Important: A repeated row is not always an error. Check whether it represents a genuine separate transaction before removing duplicates.

5. Explore the Power Query Editor

Image 2 — Power Query Editor Layout
Power Query Editor Queries StudentSales Query list Data Preview ID Name Course City FeesPaid 101 Samadhan Java Pune 45000 102 Samiksha SQL Mumbai 60000 103 Pradnya Python Pune 32000 104 Omkar C# Mumbai 75000 Query Settings Properties Name: StudentSales Applied Steps Source Promoted Headers Changed Type Trimmed Text

Simplified illustration. Actual editor layout varies by Excel version.

The Query list shows available queries, the data preview displays transformed data, and Applied Steps records the transformations performed in the current query.

6. Remove Unnecessary Columns

Sometimes a source contains columns that are not needed in the final report. Power Query can remove them without manually editing every row.

Steps
  1. Open the query in Power Query Editor.
  2. Select the column you do not need.
  3. Right-click and select Remove, or use the relevant ribbon option.
  4. Review the preview to confirm that only the intended column was removed.

Example: If the final report needs StudentID, StudentName, Course, and FeesPaid, you may remove City only if the report does not need it.

7. Clean Text Using Power Query

Imported text may contain unwanted leading or trailing spaces. Power Query provides text transformation options such as Trim and Clean.

Before Operation Expected Result
Samadhan Trim Samadhan
Text containing unwanted control characters Clean Text with removable control characters removed
java Capitalize Each Word, if appropriate Java
Steps to Trim Text
  1. Select the relevant text column, such as StudentName.
  2. Open the Transform tab or the available text transformation menu.
  3. Choose Format → Trim, where available.
  4. Check the preview and confirm that the spaces have been removed.
Tip: Text cleaning helps make matching and grouping more reliable. Do not change capitalization or remove characters if they are meaningful in your source data.

8. Before and After Data Cleaning

Image 3 — Cleaning Student Names
Before Trim StudentName " Samadhan " " Pradnya " " Omkar " Trim After Trim StudentName Samadhan Pradnya Omkar Illustration of trimming leading and trailing spaces.

9. Change Data Types

Data types determine how values are interpreted. Power Query allows you to assign appropriate types such as Text, Whole Number, Decimal Number, Date, and Date/Time.

Column Suggested Type Reason
StudentID Text or Whole Number, depending on requirements Text is safer when leading zeros or non-numeric identifiers matter.
StudentName Text Stores names.
Course Text Stores course labels.
FeesPaid Whole Number or Decimal Number Allows numeric calculations when values are valid.
AdmissionDate Date Allows date-based filtering and analysis.
Steps
  1. Select the column header.
  2. Open the data type menu, often shown by an icon beside the column name.
  3. Select the correct type.
  4. Review the preview for errors or unexpected conversions.

10. Filter Rows in Power Query

Filtering allows you to keep only the records needed for a report. For example, you can filter the City column to keep only Pune records.

Steps to Filter Pune Students
  1. Select the filter dropdown on the City column.
  2. Clear the values you do not want to keep.
  3. Select Pune.
  4. Apply the filter.
  5. Review the remaining rows in the preview.
Important: A query filter changes which records are included in the query output. Keep the original source data unchanged unless you intentionally need to edit it.

11. Append vs Merge Queries

Power Query supports two important ways to combine data: Append and Merge. They solve different problems.

Image 4 — Append and Merge Compared
APPEND MERGE January 101 • Java 102 • SQL February 103 • Python 104 • C# Combined Rows 101 • Java 102 • SQL 103 • Python 104 • C# Stacks records vertically Students 101 • Samadhan 102 • Samiksha Fees 101 • 45000 102 • 60000 Matched Columns 101 • Samadhan 102 • Samiksha 45000 60000 Matches records using a key Best for similar tables with the same kind of columns. Best for related tables with a matching key.

Custom conceptual illustration. Check column names and join settings before combining real datasets.

Append Queries

Append adds rows from one table below another. It is useful when two tables have similar columns, such as January and February admissions.

Merge Queries

Merge matches records between tables using one or more key columns. For example, merge StudentDetails and FeeDetails using StudentID. The join kind determines which matching and non-matching records remain.

Easy way to remember: Append adds rows; Merge adds related columns based on matching values.

12. Load and Refresh Query Results

Load the Data
  1. Review the query preview and applied steps.
  2. Select Close & Load or the equivalent available command.
  3. Choose a worksheet or supported data model destination when offered.
  4. Check the output for missing columns, errors, or unexpected rows.
Refresh the Query
  1. Update the source file or source records as required.
  2. Ensure that the source remains accessible.
  3. Use Refresh or Refresh All in Excel.
  4. Review the refreshed output and check for errors.
Important: Refresh does not guarantee that every connection will succeed. File paths, permissions, credentials, source structure, and connector availability can affect the result.

13. Practical Exercises

Task 1: Import a CSV File

Import StudentSales.csv through Data → From Text/CSV and inspect the column types in Power Query Editor.

Task 2: Trim Student Names

Apply Trim to StudentName and verify that unnecessary leading and trailing spaces have been removed.

Task 3: Filter Pune Records

Filter City to retain Pune records and confirm that the output contains only the intended rows.

Task 4: Append Two Tables

Create January and February tables with matching columns and append them into a combined table.

Task 5: Merge Student and Fee Tables

Merge two tables using StudentID, expand the fee information, load the result, and test refreshing the query.

Important Points to Remember

  • Power Query supports repeatable import and transformation workflows.
  • Applied Steps records the transformations in a query.
  • Trim and Clean solve different text-cleaning problems.
  • Set data types carefully before analysis.
  • Append stacks rows; Merge matches related records.
  • Check join keys and duplicate records before combining tables.
  • Refresh the query and validate the result after source changes.

Summary

Power Query helps import, clean, transform, combine, and load data from supported sources. Its Applied Steps make data preparation repeatable, reducing the need to manually perform the same tasks each time a source changes.

Use Trim to remove unwanted surrounding spaces, set suitable data types, filter records carefully, and choose Append or Merge according to the structure of your data. Always validate the final output after loading or refreshing.