Changing the cell's display format afterward cannot repair a date that was interpreted incorrectly. The decision belongs at the conversion step.
Find a row that distinguishes the conventions
This original fictional supplier extract has one row per delivery. The supplier has confirmed day/month/year notation:
delivery_id,delivery_date_text
D01,03/04/2026
D02,17/04/2026
D03,31/04/2026
D01 should mean April 3, 2026. D02 is April 17 and helps expose an inappropriate month/day interpretation because 17 cannot be a month. D03 is invalid under the confirmed convention: April has no day 31. Keep that failure visible for correction by the source owner.
Without the supplier's confirmation, D01 remains ambiguous. D02 can suggest a convention for some rows, but it cannot prove that a file assembled from several sources uses one rule throughout.
Microsoft's Date.FromText reference, checked October 24, 2026, documents Format and Culture options. Those options make a known rule explicit; they cannot discover which date the supplier intended.
Request an explicit conversion and an error column
For a single value under the confirmed source convention, the illustrative M expression is:
Date.FromText("03/04/2026", [Format="dd/MM/yyyy", Culture="en-GB"])
The expected calendar date is #date(2026, 4, 3). Here en-GB describes the parsing convention of this source, even when the analyst is working in the United States. Do not choose en-US merely because the report audience is American.
Ask ChatGPT to adapt that rule to your existing query. Supply the actual step and column names so it does not replace a whole query with a disconnected example. Preserve delivery_date_text and add a parsed column plus a separate error indicator or review table. Replacing every conversion failure with a blank would hide D03's invalid source value.
Review this Power Query date-conversion step.
Host application/version and relevant existing M steps: [PASTE]
Raw date column name and its current type: [ENTER]
Permitted sample rows with stable IDs: [PASTE]
Source-confirmed date format/culture and evidence: [ENTER OR UNKNOWN]
Required output: original text, parsed date, visible parse-failure status.
If the source convention is unknown, identify ambiguous values and ask
what evidence would resolve them before proposing a final conversion.
If confirmed, use an explicit Date.FromText Format and Culture.
Keep IDs and raw text unchanged. Show expected dates for one ambiguous,
one unambiguous and one invalid input. Do not silently swap day/month,
drop invalid rows, fill failures with today, or claim a refresh ran.
Return a minimal proposed M patch with its insertion point.
This is a task-specific use of OpenAI's prompting guidance: include the context that changes the answer and state what must survive the edit.
Check the calendar value before formatting the report
In a copy of the maintained query, inspect year, month and day for the test rows rather than relying only on a formatted date string. The authored expectations are D01=2026-04-03, D02=2026-04-17 and D03=conversion failure. No Power Query refresh is claimed here.
Also check where an earlier automatic type conversion occurred. If raw text has already become a date under the wrong rule, applying a new display format later is too late; review the earlier conversion against the retained source text.
The Power BI merge guide handles the separate problem of repeated lookup keys multiplying rows. For this date repair, retain the confirmed source convention and the invalid-row review beside the query, then verify the same three cases after the next real refresh.