Convert Text to Date (Multiple Formats) in Power Query M

Tries several known date formats in order and returns the first one that parses successfully.

Date & TimeAdvanceddatemultiple formatsparsefallback

Power Query M code

let
    Source = YourTable,
    AddedDate = Table.AddColumn(Source, "ParsedDate", each
        try Date.FromText([DateText], "en-US") otherwise
        try Date.FromText([DateText], "en-GB") otherwise
        try #date(Number.From(Text.Start([DateText], 4)), Number.From(Text.Middle([DateText], 4, 2)), Number.From(Text.End([DateText], 2))) otherwise
        null,
        type date)
in
    AddedDate

Generate a date table in Power Query M

Notes

Add or reorder the try/otherwise branches to match the date formats actually present in your source.

Requirements

How to use this snippet

  1. In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
  2. Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
  3. Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Convert Text to Date (Multiple Formats).