PQ - Transforming Text to Date in Power Query

(question from the web)

Quote:

---

I ma trying to transfomr an 8 digit text to a Date in Power Query. For instance, I want to transform in Power Query 02012021 to 02/01/2021 and format it
as a date?

---

Text		Text	Date
02012021		02012021	02/01/2021

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Date = Table.AddColumn(Source, "Date", each
        Date.FromText
        (
        Text.Format("#[day]/#[month]/#[year]",
            [
            day = Text.Start([Text],2),
            month = Text.Middle([Text],2,2),
            year = Text.End([Text],4)
            ],
        "en-GB")
        )
    ),
    Type = Table.TransformColumnTypes(Date,{{"Date", type date}})
in
    Type

---

I don't know if there's any advantage with the following except for it being easy to change whether the source data is in US or UK date format:

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedTypewithLocale = Table.TransformColumnTypes(Table.TransformColumns(Source, {{"Text", each Text.Insert(Text.Insert(_,4,"/"),2,"/"), type text}}), {{"Text", type date}}, "en-US")
in
    ChangedTypewithLocale

---

by changing en-US to en-UK

Attachment 3517

Quote:

---

Originally Posted by p45cal

by changing en-US to en-UK

---

DataFormat.Error: The specified culture is not supported. Details: en-UK

I suggest: en-GB