I’m standardizing all inbound files to YYYY-MM-DD using a staging sheet and Power Query before anything hits the master table. We have 5 vendors with mixed MM/DD/YYYY, DD/MM/YYYY, and text dates — do you enforce the rule at the source or normalize in staging, and how do you document it so entry stays consistent?
I’d add a tiny vendor map (Vendor, Culture) and in Power Query parse with Date.FromText per vendor, then format to ‘YYYY-MM-DD’ — like a universal power adapter. Document it as a one-page data contract in the staging file and in the query descriptions, and still ask new vendors for ISO 8601 at the source (ISO 8601 - Wikipedia) so drift stays low.
Normalize in staging but add an exceptions path: use try … otherwise in Power Query (https://learn.microsoft.com/powerquery-m/try-otherwise) to parse with the expected culture per file, log failures with raw value/vendor to an Errors sheet, and block the load — a bouncer for dates… Document a simple rule like “reject ambiguous dates” with one good/bad example, and if you can influence vendors, require a tiny ‘DateFormat: YYYY-MM-DD’ header; otherwise add a FormatHint field on intake so no one guesses MM/DD vs DD/MM. A lightweight header or manifest keeps humans from guessing and makes the audit trail obvious.
Quick thing that’s worked for me: in the staging sheet I inject a sentinel row for each of the 5 vendors with a date like 13–02-2024 and assert the parsed day is 13; if it isn’t, the load fails and the vendor gets the spec. I also persist a ‘DateKey’ (e.g., 20240213) in the master so later steps don’t depend on locale — would that fit your setup?
I keep a rolling ‘min/max date’ guardrail in the staging sheet: anything parsed outside, say, last FY through next quarter gets flagged and quarantined before it hits the master, which catches day/month flips fast. I also auto-generate a one-line summary per vendor (parsed count and flagged count) and save it next to the load as living docs; curious if that fits your 5‑vendor setup.
I standardize in staging, but make it deterministic with a tiny Vendor→Culture config: Power Query merges that and parses via Date.FromText using the mapped culture (Date.FromText - PowerQuery M | Microsoft Learn). I also emit the config as a “Vendor Date Map” CSV next to each load so the rule’s visible; I do nudge vendors to fix upstream, but I don’t depend on it — would that work with your five?
I handle it in staging and add a hard upload gate: if a sample row can’t round‑trip to ISO 8601 (yyyy‑MM‑dd) and back to the same value, the file is rejected with a vendor‑specific error. I keep a one‑pager per vendor (“Date: YYYY‑MM‑DD”, min/max window, timezone) versioned next to the ETL so diffs are obvious; @dashgrove’s culture map slots into that. Would you push a locked template with a ‘Date (YYYY‑MM‑DD)’ header or drop a clear ISO 8601 link so the rule’s unmissable at source?