Modern Excel and Power ToolsModule 3: Power Query ETL — Extract, Transform & Load

Power Query Basics: Import, Transform & Load (ETL) Pipeline Setup

Understand the Power Query ETL engine, master the Applied Steps pipeline, and transform messy source data into pristine automated tables.

Free preview

You are previewing a full lesson.

This lesson is a free preview. No account is needed to read it.

Interactive Study Modes:Switch between stacked, split-screen workbench, or cinematic theater mode.

Power Query Basics: Import, Transform & Load (ETL) Pipeline Setup

The video for this lesson has not been published yet. The written lesson below is the full material for this session.

Open VBA EditorLaunches the Visual Basic Editor to inspect, debug, and run macro code.

⌥AltF11

Launch Power Query EditorOpens Power Query window to transform, clean, and reshape incoming data.

⌥AltAPT

Insert PivotTableCreates an instant summary PivotTable from any structured data table.

⌥AltNV

Reference Spill RangeAppends the hash symbol to dynamically capture the entire spilled array range.

#

Evaluate Formula Step-by-StepSteps through complex nested formula logic to debug errors and intermediate results.

⌥AltMV

Interactive Spreadsheet Practice Sandbox

Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.

⚡ Power Tools & Macros Note: In-browser sandbox simulates Excel formula syntax and math logic. For compiled VBA macros and native Power Query M-code transforms, please open the practice workbooks provided above in desktop Microsoft Excel (365 / 2021).

Formula practice lab

Exercise

Exercise 1: Basic SUM Addition

Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).

Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.

Hint:Use formula =SUM(D2:D5)

Exercise 1: Basic SUM Addition practice sheet
#	A	B	C	D
1	Product	Quantity	Unit Price	Total
2	Laptop	5	45000	225000
3	Mouse	20	500	10000
4	Keyboard	15	1200	18000
5	Monitor	8	12500	100000
6	Grand Total

Lesson notes & syllabus guide

Power Query ETL: Extract, Transform & Load

Agar aap har subah Excel open karke column headers promote karte hain, blank rows delete karte hain, dates ka format change karte hain, aur text trim karte hain — toh aap Power Query ke bina 50% time waste kar rahe hain. Power Query Excel ka built-in data transformation engine hai jo har step ko ek automated pipeline me record karta hai.

1. The 3 Phases of Modern ETL

Extract (Get Data): CSV, Excel workbooks, SQL Server, SharePoint, ya Web APIs se data pull karna.
Transform (Power Query Editor): Data types fix karna, unpivot karna, columns split karna, nulls replace karna.
Load (Close & Load): Transformed clean data ko Excel Table ya Power Pivot Data Model me load karna.

2. The Applied Steps Pipeline

Power Query GUI me aap jo bhi action karte hain (e.g., Remove Top Rows, Change Type, Filter Rows), wo right-side panel me Applied Steps me save ho jata hai.

Yeh steps peeche M-Code (Power Query Formula Language) generate karte hain.
Naya raw data aane par aapko sirf Data > Refresh All (⌃Ctrl⌥AltF5) press karna hota hai, aur saare 20 cleaning steps 2 seconds me replay ho jate hain!

Mahindra & Mahindra dispatch log cleaning pattern in M-Code:

let
    Source = Csv.Document(File.Contents("C:\Data\Mahindra_Dispatch.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Dispatch_ID", type text}, {"Tractor_Model", type text}, {"Dispatch_Date", type date}, {"Quantity", Int64.Type}, {"Unit_Price_INR", Currency.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Quantity] > 0)
in
    #"Filtered Rows"

[!TIP] Power Query me raw source file kabhi modify nahi hoti! Original raw file 100% untouched rehti hai, isliye audit compliance ke liye yeh gold standard tool hai.

Lesson resources

Download the workbooks and cheat sheets used in this lesson.

Mahindra & Mahindra Farm Equipment Regional Dispatch Logs — Practice Workbookmahindra_farm_equipment_dispatch_before.xlsx

Power Query ETL Fundamentals Quiz

Pass mark 70%

5 questions on ETL concepts, Applied Steps execution, and M-code immutability.

This quiz has 5 questions. Sign in to answer them — results are saved to your account.

Mahindra Farm Equipment ETL Pipeline Setup

Community discussion

Lesson Q&A & Peer Discussions

Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.

No questions yet on this lesson

Be the first to ask a doubt or start a discussion with peers and mentors!

Product

Quantity

Unit Price

Total

Laptop

45000

225000

Mouse

500

10000

Keyboard

1200

18000

Monitor

12500

100000

Grand Total

Power Query ETL: Extract, Transform & Load

1. The 3 Phases of Modern ETL

Extract (Get Data): CSV, Excel workbooks, SQL Server, SharePoint, ya Web APIs se data pull karna.

Transform (Power Query Editor): Data types fix karna, unpivot karna, columns split karna, nulls replace karna.

Load (Close & Load): Transformed clean data ko Excel Table ya Power Pivot Data Model me load karna.

2. The Applied Steps Pipeline

Power Query GUI me aap jo bhi action karte hain (e.g., Remove Top Rows, Change Type, Filter Rows), wo right-side panel me Applied Steps me save ho jata hai.

Yeh steps peeche M-Code (Power Query Formula Language) generate karte hain.

Naya raw data aane par aapko sirf Data > Refresh All (⌃Ctrl⌥AltF5) press karna hota hai, aur saare 20 cleaning steps 2 seconds me replay ho jate hain!

Mahindra & Mahindra dispatch log cleaning pattern in M-Code:

let Source = Csv.Document(File.Contents("C:\Data\Mahindra_Dispatch.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Dispatch_ID", type text}, {"Tractor_Model", type text}, {"Dispatch_Date", type date}, {"Quantity", Int64.Type}, {"Unit_Price_INR", Currency.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Quantity] > 0) in #"Filtered Rows"

[!TIP] Power Query me raw source file kabhi modify nahi hoti! Original raw file 100% untouched rehti hai, isliye audit compliance ke liye yeh gold standard tool hai.