Power Query I — Import & Clean

Learning objectives

By the end you can:

1. Import data from the four workhorse sources (From Text/CSV, From Workbook, From Folder, and From Web) via their exact ribbon paths, and explain what each returns into the Power Query Editor.
2. Read and edit the Applied Steps pane fluently, understanding that every step is one recorded, re-runnable transformation, and that the query is the ordered list of them, not the data preview you see.
3. State why the first thing a query should do is set data types, and set them correctly (including locale-aware typing) so that later steps and the final load behave predictably.
4. Perform the core column transforms (Split, Merge, Trim, Clean, and Fill Down/Up) from their ribbon locations, and say what problem each one solves.
5. Unpivot a wide "attribute-across-the-top" crosstab into tidy long form (and Pivot back), and explain why tidy data is the shape every downstream tool wants.
6. Aggregate inside the query with Group By, producing sums, counts, and other rollups without a single worksheet formula.
7. Distinguish Append (stacking same-shaped tables into more rows) from Merge (joining tables side-by-side on a key), choose the correct join kind (left outer, inner, and the rest), and know that append requires identically named columns.
8. Explain query folding (pushing transformation work back to the source) why it matters for speed, and how to check for it with View Native Query.
9. Choose the right Close & Load To destination (worksheet table, connection-only, or the Data Model) and practise refresh discipline so your outputs never silently lag the source.
10. Diagnose the classic Power Query failures, untyped data and locale chaos, case-sensitivity bites, append column-name mismatches, wrong join kinds, loading a huge query to a sheet, and editing a source without refreshing.

Prerequisites & connections

Builds on. EX0.01 (Tables & structured references, Ctrl+T; Power Query loves a real Table as a source and returns real Tables, and the "one row per observation" habit begins there), EX1.01/EX1.02 (the aggregation and lookup families, because Group By is the query-side cousin of SUMIFS, and Merge is the query-side cousin of XLOOKUP/INDEX+MATCH; knowing the worksheet versions makes the query versions click instantly), and EX2.01 (Data Tools & Integrity, data types and validation, the same "clean at the boundary" instinct). You do not need any coding: Power Query is driven by ribbon and mouse, and the M language underneath is something you read long before you ever write it.

Feeds forward. EX4.02 (Power Query II) takes the recipe idea further, parameters, custom columns in M, conditional and fuzzy merges, and turning transformations into reusable functions. EX5.01 and EX5.02 wire a refreshed query into the Data Model and PivotTables so a single refresh updates an entire dashboard. On the analyst track, this is the plumbing under everything: M2.05's peer-benchmark template ingests raw exports and appends new quarters; M3.08's three-statement model pulls actuals from a monthly folder; every "living" report that updates with one click (EX7.01) is a Power Query pipeline underneath. The tidy-data move you learn here (unpivot) is the single most valuable reshape in analytics, and you will reach for it in nearly every real project.

Get Data and the Applied Steps pane

First principles. "Getting data" in Power Query means opening a connection to a source and pulling a copy of it into the Power Query Editor, a separate window where you build your recipe. The source is never modified. The four sources that cover the overwhelming majority of an analyst's life:

• From a single CSV: Data ▸ Get Data ▸ From File ▸ From Text/CSV (or the shortcut button Data ▸ From Text/CSV). Excel shows a preview with a detected delimiter and file origin (encoding); click Transform Data to open the editor rather than Load, so you can clean before anything lands on a sheet.
• From an Excel workbook: Data ▸ Get Data ▸ From File ▸ From Workbook. A Navigator pane lists every sheet and named Table in the file; tick what you want and click Transform Data.
• From a whole folder of same-shaped files, Data ▸ Get Data ▸ From File ▸ From Folder. This is the one that changes lives: point at a folder, and every file in it (this month's, last month's, next month's) is available to be combined into one table. Drop a new file in the folder, refresh, and it appears. Worked example 1 does exactly this.
• From a web page: Data ▸ Get Data ▸ From Other Sources ▸ From Web (or Data ▸ From Web). Paste a URL; the Navigator lists the HTML tables Power Query found on the page; pick one and Transform Data.