Business Intelligence with Power BI

L02 · Power Query I: Shape and Clean

Practice — Medium · Phones and First Names

Skills focus. Replace Values on punctuation · Split Column by delimiter · step ordering — and discovering, mid-recipe, why order matters.

Ungraded. In-class or self-paced. Less scaffolding: the tasks say what, you find the buttons.

Scenario

The customer file from Mild is clean. Now two teams file requests within an hour of each other. The call-center wants every phone number in one dial-ready format: 517-555-0142 style, nothing else. The email team wants a FirstName column, because "Dear Avery" outperforms "Dear Avery Thorne." Extend your Mild recipe — do not start over.

Tasks

  1. Reopen your Mild practice file (or rebuild its recipe quickly) and enter the Power Query Editor.
  2. Standardize the Phone column. The file writes phones three ways — (517) 555-0142, 517-555-0142, and 517.555.0142. Using only Replace Values steps, make every number match the dashed format. Checkpoint: the column profile shows every value 12 characters long — check the profile's length statistics, or sort by the column and inspect both ends.
  3. Split the name. Create FirstName and LastName columns from Name using Split Column by delimiter (the space), then rename the results. Checkpoint: no errors, 500 rows, and spot-checks look right at both ends of the alphabet.
  4. The ordering question. Click your Split Column step and drag it above the Trimmed Text step in the Applied Steps list, then look at the LastName column's values carefully. Undo (drag it back). Write one sentence about what you saw.
Hint — phone replacements: which three?

Think in punctuation, not digits: replace . with -; replace ( with nothing; replace ) (close-paren plus the space) with -. Order among these three does not matter — none creates text another consumes. Step 4 shows you a pair where order very much does.

Hint — my split produced three columns for some rows

Check the split options: "at the left-most delimiter" splits once, giving exactly two columns. The default "at each occurrence" splits every space — fine for these names until it isn't. Left-most is the deliberate choice here.

Reflection

  • Both fixes were Replace Values and Split — no new concepts, just composition. What made step 4's reorder break things when the phone replacements' order was interchangeable?
  • The call-center's request took three recorded steps. What would the same request have cost in a 500-row spreadsheet, and what would next month's export make of that effort?