A date column can contain no errors and still put work in the wrong month.
The string 04/05/2026 supports two familiar readings. Under month/day/year, it means April fifth. Under day/month/year, it means May fourth. A successful conversion cannot tell you which date the sender intended.
Choose the source convention before converting the column. Preserve the original text beside the interpreted date. Keep the evidence that connects the two.
The procedure below concerns calendar dates in Power Query M. It does not convert time zones or choose which business event a report measures. The worksheet is a proposed review method. Every example record and source declaration is fictional.
Preserve the Text Before Assigning a Date Type
Start with an untouched file and a working import. Record the source version and the field name. Keep the date field as text until you have an interpretation rule.
Inspect the query's existing steps before adding another conversion. For unstructured sources, Power Query can infer types from an initial sample and add a Changed type step. Review whether that step already converted the field. Microsoft: automatic type detection.
If an earlier step has converted the date, return to the intact text at the source boundary. Making a date look like text afterward does not establish what characters arrived.
Use separate fields for source_date_text and parsed_date. Attach the source convention to the import configuration. Do not overwrite the evidence column when you accept the conversion.
Keep record identifiers as text too. An order code that resembles a date still carries identity. The procedure for preserving identifiers during CSV import covers that separate boundary.
This separation gives a reviewer three things to inspect: what arrived, what rule ran, and what calendar date resulted.
Establish the Convention From Source Evidence
A filename containing a country code does not define a date column. Neither does the location of the person who sent it.
Ask for the export specification or a declaration from the source owner. Record the field's order, separator, year width, and applicable calendar. Include whether the declaration covers the whole file or a named partition.
For a repeat handoff, put this information in the CSV data contract. A useful declaration for this exercise is: service_date uses Gregorian day/month/year, two digits for day and month, and four for year.
An unambiguous row can challenge a proposed rule. The synthetic string 22/05/2026 cannot represent month twenty-two. It is consistent with May twenty-second under the declared day-first rule. It does not prove that every other row follows that rule.
Treat the declaration and the sample as different evidence. The declaration states the intended convention. The sample checks whether selected records conform. A single compatible row cannot establish the origin of the remaining records.
If the sender cannot establish a convention, hold the ambiguous records. Record what is missing and who can answer. Familiarity is not evidence.
State Format and Culture in Date.FromText
Microsoft's Date.FromText reference documents an options record with Format and Culture. A missing or null format uses best-effort parsing. An omitted culture uses Culture.Current.
For the fictional day-first declaration, make both choices visible:
Date.FromText("04/05/2026", [Format = "dd/MM/yyyy", Culture = "en-GB"])
The intended result is #date(2026, 5, 4), May fourth. Under an explicitly month-first declaration, the expression changes:
Date.FromText("04/05/2026", [Format = "MM/dd/yyyy", Culture = "en-US"])
The intended result is #date(2026, 4, 5), April fifth. These expressions demonstrate different declared contracts. They are not competing guesses to run until one produces an acceptable report.
In these patterns, dd represents a two-digit day, MM a two-digit month, and yyyy a four-digit year. Case matters: lowercase mm represents minutes. Microsoft: custom date and time format strings.
Culture also affects textual month interpretation and calendar conventions. Confirm it alongside the format. A numeric pattern alone does not define every calendar assumption. Microsoft: Date.FromText options and calendar example.
These examples use the documented M interface checked on October twenty-eighth, 2026. Record your Power Query host and installed version when applying them. They are documented expressions, not evidence of a completed refresh in your environment.
Separate Parsing From Display Formatting
Parsing assigns a calendar value to text. Display formatting controls how an already assigned value appears.
For this exercise, write expected dates in year-month-day order during review. Label that review convention above the worksheet. Do not infer the stored value from a familiar-looking display alone.
Power Query's Using locale command lets you choose a data type and locale for a column conversion. Microsoft also recommends extracting year, month, and day to verify converted dates. Microsoft: locale and date verification.
For the fictional May fourth record, the components should be year 2026, month five, and day four. If the components show month four and day five, changing the display will not repair the interpretation.
Record the observed components next to the expected components. Accept the row when they agree under a supported convention. A date that looks plausible still needs that comparison.
Keep this check separate from event time and processing time. Correctly reading May fourth does not establish whether the field means service completion, receipt, or report preparation.
Copy a Date-Import Worksheet
Create one worksheet per source convention. Put the file version, field, source owner, rule version, and review date above it. Retain a record key with each row so the exception can return to its source.
| Original text | Convention evidence | Format | Culture | Expected date | Parsed date | Disposition |
|---|---|---|---|---|---|---|
| [Exact source characters] | [Specification version or owner declaration] | [Explicit pattern] | [Declared culture] | [Independent calendar date] | [Observed value and components] | [Accept or hold, with reason] |
| [Next record] | [Evidence or missing] | [Rule or unresolved] | [Culture or unresolved] | [Date or unresolved] | [Value, error, or not attempted] | [Owner and next action] |
Expected date means an independently established expectation. Copying the parser's output into both columns proves nothing. Use a source record, a declared synthetic answer, or another agreed authoritative check.
Keep the actual parsed result in its own column. Record errors without replacing them with a plausible date. When you have not run a conversion, write “not attempted” rather than inventing an observation.
Use disposition to preserve the decision. An accepted row names its supporting rule. A held row names the unresolved condition. A reviewer should not need to reconstruct the reason from an empty cell.
Walk Through a Synthetic Mixed Delivery
Suppose a fictional supplier sends four service-date strings. Its current export declaration specifies day/month/year and the Gregorian calendar. The fourth row arrives in a separate attachment without convention metadata.
The following parsed results are expected demonstration results, not a log from a live business system.
| Original text | Convention evidence | Format | Culture | Expected date | Parsed date under proposed rule | Disposition |
|---|---|---|---|---|---|---|
04/05/2026 |
Fictional export declaration A | dd/MM/yyyy |
en-GB |
2026-05-04 | 2026-05-04 | Accept after observed result agrees |
22/05/2026 |
Same declaration A | dd/MM/yyyy |
en-GB |
2026-05-22 | 2026-05-22 | Accept after observed result agrees |
05/04/26 |
Conflicts with four-digit-year rule | Unresolved | Unresolved | Unresolved | Not attempted | Hold for full year and convention |
05/04/2026 |
Separate attachment; no declaration | Unresolved | Unresolved | April fifth or May fourth | Not attempted | Hold for source evidence |
The first two rows support a narrow acceptance decision. Under the fictional declaration, May fourth and May twenty-second belong to the same calendar month. The second row makes a month-first interpretation visibly unsuitable.
The third row fails the proposed contract before any parser behavior matters. Request a four-digit year from the source. Do not add a century rule because the surrounding records happen to mention 2026.
The fourth row has a valid-looking shape. It remains unresolved because its source convention is missing. Applying declaration A to an unrelated attachment would invent evidence.
If the owner later confirms that attachment as month-first, give it a separate import rule. Preserve the confirmation and the partition boundary. Do not change the rule halfway through one column based on whether a row produces an error.
Hold Mixed Conventions Without Guessing
A mixed column needs a source decision. Ask whether the file combines exports, whether a field definition changed, or whether specific rows were edited. Those are questions to investigate, not explanations to assume.
If the source can identify partitions reliably, convert each partition under its documented rule. Retain the partition key. Keep the original text and the applied rule version on every interpreted record.
If no reliable partition exists, hold the affected batch. Counting accepted and unresolved records separately gives the owner a concrete reconciliation task. Quietly dropping unresolved dates makes the report boundary harder to explain.
Reject a “try day-first, then month-first” acceptance rule for this worksheet. It lets ambiguous rows pass under whichever rule happens to run first. Parser success supplies no missing declaration.
Review two-digit years and incomplete dates as contract exceptions. Ask for the missing information. Do not fill a month, day, or century from the import date unless an explicit business rule requires it.
Retain the Decision With the Next Import
Give the next importer a short challenge set alongside the rule. Include 04/05/2026, 22/05/2026, an incomplete year, and a record with no convention evidence. Write the expected disposition before running the import.
The challenge set should exercise acceptance and restraint. The ambiguous string needs the declaration. The unambiguous day checks the chosen order. The incomplete year and undocumented record should remain held under this proposed method.
Ask a second reader to explain the first result without looking at the formatted display. The answer should name May fourth, the day-first rule, and declaration A. “It converted without an error” leaves the central question unanswered.
If the reader cannot trace a result to its rule, revise the worksheet before expanding the import. A repeatable explanation matters more than a familiar date appearance. Keep the review limited to the supplied evidence and its calendar interpretation.
Before accepting the handoff, compare the source record count with accepted, held, and explicitly excluded records. Check ambiguous and unambiguous samples. Inspect the calendar components independently.
Retain the source declaration, query configuration, worksheet, and exception decisions together. Give the next reviewer enough information to repeat the interpretation without contacting the original importer.
When the sender changes its format, revise the rule version before accepting the next delivery. Recheck the synthetic examples against the changed rule. Preserve earlier decisions under the convention that produced them.
A clean date column carries its interpretation evidence.
Sources
- Microsoft: Data types in Power Query. Automatic type detection, Using locale, and date-component verification. Checked 2026.10.28.
- Microsoft: Date.FromText. Format, Culture, defaults, and calendar behavior. Checked 2026.10.28. No local Power Query runtime was used.
- Microsoft: Custom date and time format strings. Day, month, minute, and year pattern tokens. Checked 2026.10.28.