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

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+⌥Alt+F5) 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.