Day 13: Power Query — Basics

Power Query is Excel's built-in ETL (Extract, Transform, Load) tool, accessible via Data → Get Data. It records every transformation step in a query that can be refreshed with one click when source data updates. Benefits over manual cleaning: (1) Reproducible — the same steps run every time on new data. (2) Auditable — every step is listed in the Applied Steps pane and can be reviewed or removed. (3) Scalable — handles datasets that would slow down Excel formulas. (4) No formulas required — most transformations are point-and-click.

Data → Get Data → From File → From Text/CSV → browse to the file. Power Query detects the delimiter and data types automatically. In the preview window, you can change data type detection or adjust the delimiter if needed. Click "Transform Data" to open the Power Query Editor for further cleaning, or "Load" to import directly. Once loaded, the query is saved — next time you click Refresh, it re-reads the file and applies all your transformation steps.

Applied Steps is the pane on the right side of the Power Query Editor that lists every transformation you have performed, in order. Each step has a name (e.g. "Removed Columns", "Changed Type", "Filtered Rows"). You can click any step to see the state of the data at that point. You can delete a step to remove that transformation. You can rename steps for clarity. This step-by-step recording is what makes Power Query transformations reproducible and auditable.

In the Power Query Editor: select the column(s) to check for duplicates → Home → Remove Rows → Remove Duplicates. If you select one column, rows are considered duplicates only if that column value repeats. If you select multiple columns (Ctrl+click), a row is a duplicate only if ALL selected columns match. Power Query adds a "Removed Duplicates" step to Applied Steps — you can delete it if you change your mind.

With both tables loaded as queries: Home → Merge Queries. Choose the first query, select the matching column, choose the second query, select its matching column, choose the join type (Left Outer, Inner, Full Outer, etc.). The result creates a new column containing a nested table — click the expand icon in the column header to select which columns from the second table to bring in. This is the Power Query equivalent of a SQL LEFT JOIN.