📌 Stage 1 · Connect · Power Query · Folder import · 35–45 min

Point Excel at the folder and stop opening files one by one

Learning goals

  • Set up a source folder with a file-naming convention the queries can rely on
  • Build a From Folder import that combines every monthly CRM CSV into one table
  • Explain why staging the raw text beats opening and pasting CSVs by hand
  • Find the duplicate row, the unreadable amount, and the region spellings in the staged data

Concepts

One folder is the contract

Power Query's best small-business feature is Folder.Files: point one query at a folder and it reads every file in it, today and forever. Northgate keeps the exports in C:\Reports\Exports and lets each system name its own files: crm_2026-07.csv, crm_2026-08.csv from Fieldlight, ledgerly_invoices.csv from Ledgerly, and clockwork_hours.csv from Clockwork. The CRM query does not hard-code a month; it takes every file whose name starts with crm_, so September's export simply joins the folder and gets picked up on the next refresh.

The naming convention is doing real work here. 'Starts with crm_ and ends with .csv' is a filter the query can apply with Text.StartsWith and Text.EndsWith -- which is exactly what the CRM Export script on the M Code Reference sheet does as its second step.

💡 Tips:

  • Keep the folder out of OneDrive or Dropbox sync folders if you can: sync locks files mid-refresh and produces confusing 'file in use' errors.
  • If the CFO also drops notes or PDFs in that folder, the name filter protects the query -- but a cleaner habit is one folder per source system.

Combine, then promote the headers

From Folder returns one row per file, with the file's bytes in a Content column. The combine pipeline parses each file (Csv.Document), keeps the file name, expands the parsed tables, and only then promotes the first row of the combined result to column headers (Table.PromoteHeaders). That order matters: parse, expand, promote. Promote too early and your headers become Column1, Column2, and so on.

In the desktop editor the clicks are: Data > Get Data > From File > From Folder, browse to C:\Reports\Exports, confirm the file list, then Combine > Combine and Transform. Filter the file list to the crm_ files before combining -- the same filter survives in the query as a step you can re-read later.

=COUNTA('CRM Export'!$B$5:$B$1004)

The row-count check for the staged table: 12 opportunities. Run the same COUNTA on 'Clean Pipeline' after lesson 4 and it returns 10 -- two rows fewer, for reasons the cleaning query documents.

Staging is read-only

Look at what the CRM Export sheet actually contains: every value is text, including the Created dates and the Amounts. That is correct. A CSV has no types; Power Query assigns them later, in a step you control. The staging query's only job is to land the raw bytes with the headers promoted.

Resist the urge to fix anything here. OPP-2214 appears twice because the July file was exported twice; one Amount reads n/a; regions arrive as OH, ohio, MI, Indiana, and Kentucky; one account name carries leading spaces. All of that is next month's problem too, which is exactly why the fixes belong in the repeatable cleaning query (lesson 4), not in a one-off edit you will forget you made.

💡 Tips:

  • A duplicate row in staging is information, not garbage: it tells you the export was run twice. Fix the export process, and let the query defend you in the meantime.

Practice

The exercise opens on the raw CRM Export sheet (the BEFORE state). The AFTER state is the Clean Pipeline sheet -- one tab to the right. Nothing is saved in the browser.

  1. Read the staged rows — Rows 5 through 16 hold the twelve imported opportunities. Confirm every column is text: the Created column shows 8/3/2026 as a left-aligned string, not a date, and the Amount column shows $18,400.00 with the dollar sign and comma intact.
  2. Find the three defects — Row 10 repeats OPP-2214 (Harborview Cafe LLC) with values identical to row 9 -- the duplicate re-export. Row 11 (OPP-2215) shows n/a in the Amount column. Region spellings in column E include OH, ohio, MI (with a trailing space on row 7), Indiana, Kentucky, and Ohio (with a trailing space on row 14).
  3. Check the file evidence — The import kept the source file name in the staging model; in your own workbook the Name column proves which file each row came from. That column is your audit trail when a month's numbers look wrong.
  4. Compare with the AFTER sheet — Open the Clean Pipeline tab: ten rows, real dates, numeric amounts, consistent state names, full owner names. That is the query output you will build in lesson 4 -- nothing here was edited by hand.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Located the duplicate OPP-2214 row, the n/a amount on OPP-2215, and at least three different region spellings in the staged data
  • ☐ Confirmed the staged table holds 12 text rows while the cleaned output holds 10
  • ☐ Created (or planned) the folder C:\Reports\Exports with a naming convention each system can keep
  • ☐ Verified the baseline: 12 raw CRM rows staged, the starting point for every cleaning step in this course