Parse a Non-Standard Date Format in Power Query M

Converts a text date stored as a fixed-width string, like "20240115", into a real Date value.

Data TypesAdvanceddateparsecustom formattext

Power Query M code

let
    Source = YourTable,
    AddedDate = Table.AddColumn(
        Source,
        "ParsedDate",
        each #date(
            Number.From(Text.Start([DateText], 4)),
            Number.From(Text.Middle([DateText], 4, 2)),
            Number.From(Text.End([DateText], 2))
        ),
        type date
    )
in
    AddedDate

Generate a date table in Power Query M

Notes

Adjust the Text.Start/Middle/End offsets to match your source format's year/month/day positions.

Requirements

A text column holding dates in a fixed, non-standard layout that Date.FromText can't parse directly.

How to use this snippet

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).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
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 Parse a Non-Standard Date Format.