📌 Stage 2 · Shape · Power Query · Clean and standardize · 40–50 min

Trim, replace, deduplicate, and remove errors -- repeatably

Learning goals

  • Turn the messy CRM staging table into the ten-row Clean Pipeline output
  • Apply Trim, Replace Values, Remove Duplicates, and Remove Errors in the right order
  • Explain why cleaning belongs in the query instead of on the sheet
  • Rebuild and verify the Age Days column against the close date

Concepts

The cleaning ladder

Cleaning steps have a natural order, and the Clean Pipeline script follows it: deduplicate first (Table.Distinct on Opportunity ID -- two identical exports of the same row should count once), then trim text (Text.Trim strips leading and trailing spaces from Account, Region, and Stage), then standardize values (Replace Values, one mapping at a time), then type the columns, then remove the rows that survived typing as errors.

Remove Duplicates keys on the columns you choose -- Opportunity ID here, not the whole row. A whole-row key would keep both copies if a single character differed, which is exactly how duplicate exports sneak through. Remove Errors (Table.RemoveRowsWithErrors) then drops OPP-2215, whose Amount reads n/a and cannot become a number.

💡 Tips:

  • Run Remove Duplicates on the narrowest key that identifies a row. Broader keys fail silently; narrower keys are defensible.
  • Remove Errors is a reportable decision, not a cleanup: Northgate accepts that an opportunity with no amount leaves the pipeline value, and the Query Log says so.

Standardize with Replace Values

Region arrives as OH, ohio, MI, Indiana, Kentucky, and Ohio-with-a-trailing-space. Trim fixes the space; Replace Values maps the rest to full state names: OH to Ohio, ohio to Ohio, MI to Michigan, IN to Indiana, KY to Kentucky. Each mapping is its own step in the Applied Steps list, which is the point -- a year from now you can read exactly which spellings existed.

Owner initials get the same treatment: D. Whitfield becomes Dana Whitfield, and so on for the other five codes. In worksheet terms this is a lookup; in Power Query it is a stack of Replace Values steps (or a merge against a roster table, which is the better answer once the list grows).

=Settings!$C$7-$C5

The Age Days check column on Clean Pipeline: the close date from Settings (Aug 31, 2026) minus the opportunity's Created date. For OPP-2210, created Jul 6, 2026, that is 56 days open. Subtracting two real dates yields days -- which is why the date typing in lesson 3 had to come first.

Amounts: strip, then type

The Amount column reads 12500, $18,400.00, 9,800, and 6,400.00 -- all text. Two Replace Values steps strip the dollar sign and the comma, then a Changed Type step with the en-US locale turns the remainder into numbers. Do this after trimming, before grouping; SUM over text is zero, and Power Query will not warn you.

The finished query output is worth comparing side by side with the staging sheet: ten rows instead of twelve, real dates, numeric amounts, one spelling per state, full owner names, and a sort on Created. Nothing was edited on the sheet -- every fix is a named step that reruns next month.

💡 Tips:

  • If your own amounts can be negative (credits), strip the symbol and separators but keep the minus sign -- replacing '-' would corrupt them.
  • Sum the cleaned column the moment you type it: 123,000.00 across ten opportunities is the pipeline number the report will quote.

Practice

The exercise opens on the Clean Pipeline output (AFTER); the BEFORE state is the CRM Export tab. Cell J5 is blank -- you retype the Age Days formula. Nothing is saved in the browser.

  1. Audit the output — Rows 5 through 14 hold the ten surviving opportunities. OPP-2214 appears once (the duplicate is gone), OPP-2215 is gone entirely (its amount could not be typed), and every Region value reads Ohio, Michigan, Indiana, or Kentucky.
  2. Retype the Age Days formula — Cell J5 is empty. Type =Settings!$C$7-$C5 and press Enter: 56, the days OPP-2210 has been open at the Aug 31, 2026 close. Fill the idea down the column in your head -- the values run 56, 44, 33, 28, 24, 16, 12, 10, 5, and 3 for OPP-2220.
  3. Prove the arithmetic — Sum column G (Amount): 12500 + 18400 + 9800 + 22500 + 3200 + 11750 + 6400 + 27000 + 7300 + 4150 = 123,000.00. Four of those rows are in Proposal stage and total 66,950.00 -- numbers lessons 10 and 12 will hold you to.
  4. Compare with the BEFORE sheet — Flip to CRM Export and back. Twelve text rows in, ten clean rows out, one query, zero manual edits. That difference -- two rows -- is the whole case for cleaning in the query: it is identical next month without anyone remembering what was fixed.

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:

  • ☐ Retyped =Settings!$C$7-$C5 into J5 and got 56 days for OPP-2210
  • ☐ Confirmed the output holds 10 rows: 12 staged minus one duplicate OPP-2214 minus one unreadable OPP-2215
  • ☐ Verified the baseline: pipeline value 123,000.00 across the ten cleaned opportunities