Monthly CSV re-import that keeps corrupting IDs, dates and duplicate logic
The people-operations team exports an 8,400-row training register from its HR system as CSV and re-imports the whole file every month, when it grows by roughly 300 rows. The export mangles data in three predictable ways: employee IDs such as 00417 arrive as 417; completion dates arrive as eight-character text such as 20210315; and Department values carry trailing spaces and inconsistent casing ("ops ", "Ops", "OPS"). Each employee appears on up to six rows, one per certification, and two different employees are both recorded as "J. Silva". Expiry dates in column G drive a compliance report. The current conditional-formatting rule for expiring certifications was applied to entire columns; the file is now 60 MB and 1,900 rows show red, including several hundred certifications that were renewed last quarter. Last month a teammate used Remove Duplicates with Name selected and 40 valid certification rows disappeared. The team wants a monthly routine that is repeatable and auditable.
1. Design the import and clean-up so that IDs, dates and Department values are corrected the same way every month. Explain which tools you would use and why.
Read the worked answer
Do all the cleaning in one query so the steps replay, rather than by hand each month. Use Data > From Text/CSV > Transform Data, then Close & Load to a table.
- IDs: set the column type to Text as the first transformation, before any other step, so 00417 is never parsed as a number and stripped. If you must stay in the sheet, Text to Columns with the column format set to Text is the only reliable repair; a leading apostrophe keeps the zero but leaves text that sorts and filters oddly and infects lookups.
- Dates: 20210315 is text, not a date. In Power Query, change the type to Date and set the locale explicitly; if the locale guesser is unreliable, use a custom column
#date(Number.From(Text.Start([D],4)), Number.From(Text.Middle([D],4,2)), Number.From(Text.Right([D],2))). In-sheet, Text to Columns with the YMD date option achieves the same. - Department: Trim, then Capitalise Each Word, then a small mapping table to collapse Ops and Operations into one label. Without the mapping step, casing fixes alone still leave two spellings of one department.
Why a query: every manual pass drifts, takes half a day and leaves no audit trail. A query returns an identical structure whether the file has 8,400 rows or 11,000 rows.
2. Remove Duplicates on the Name column deleted 40 valid rows. Explain what the tool actually did and describe the correct duplicate test for this data.
Read the worked answer
What happened. Remove Duplicates compares rows against the combination of columns you select and deletes rows it considers repeats, keeping the first in current sort order. With only Name ticked, the two genuine "J. Silva" employees collapsed into one row, and every certification row belonging to the deleted person went with it. The deletion is immediate and survives saving, so Undo is only available before the file is closed.
Why the tool was the wrong question. The grain of this table is one row per employee-certification, so no row is a duplicate unless the employee and the certification repeat together.
Correct test:
- Flag with
=COUNTIFS($A$2:$A$8401,A2,$D$2:$D$8401,D2)>1beside the data, filter to TRUE, and inspect before removing anything. - Or build a PivotTable: Employee ID in Rows, Certification Code in Columns, Count of ID in Values. Any cell above 1 is a true duplicate pair, which Name can never reveal.
Practice: work on a copy, sort by Employee ID then completion date descending, and keep the most recent record rather than whichever row happened to be first.
3. Rebuild the conditional formatting so only certifications expiring within 60 days are highlighted, and explain why the file ballooned to 60 MB and produced 1,900 false positives.
Read the worked answer
Rule design. Select only the data body, for example G2:G8401, then Home > Conditional Formatting > New Rule > Use a formula:
=AND(ISNUMBER($G2),$G2-TODAY()<=60,$G2>=TODAY())
$G2locks the column and lets the row float, so each row is tested against its own expiry date.ISNUMBERexcludes blanks, text and the zero-length strings that come back from lookup formulas;<>""alone fails because an empty-string formula result passes it.- The upper bound
>=TODAY()separates "expiring soon" from "already expired". Give expired items their own rule with a different fill and tick Stop If True so a lapsed certificate is not described as expiring.
Why 60 MB and 1,900 reds: the rule was applied to whole columns, so Excel stored formats for millions of cells, and the original condition most likely tested only one side of the window, for example $G2<TODAY()+60, which matches every past date as well. That is exactly why several hundred renewed certifications showed red.
Clean-up: Conditional Formatting > Manage Rules > This Worksheet, delete the whole-column rule, then re-apply to the bounded range and re-save.