Excel Power Query: Automate Your Data Prep

Key Insights

• Power Query records your data cleaning steps and replays them on refresh
• It handles CSV, JSON, databases, APIs, and folder-based imports natively
• Merge and Append operations replace most VLOOKUP/INDEX-MATCH needs

Common Transformations

Power Query’s interface lets you:

• Split columns by delimiter
• Unpivot wide tables to long format
• Remove duplicates with one click
• Change data types consistently

M Language Basics

Behind the UI, Power Query uses M language:

let
    Source = Csv.Document(File.Contents("sales.csv")),
    Promoted = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Promoted, {
        {"Date", type date},
        {"Revenue", type number}
    }),
    Filtered = Table.SelectRows(Typed, each [Revenue] > 0)
in
    Filtered

Folder Import Pattern

Point Power Query at a folder to combine all files automatically:

1. Get Data → From Folder
2. Select your folder with monthly CSV files
3. Power Query combines them all with matching columns
4. New files added to the folder appear on next refresh