PQ - Transforming Text to Date in Power Query

[sandy666]

(question from the web (https://www.mrexcel.com/board/threads/transforming-text-to-date-in-power-query.1162407/))

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?


TextTextDate

0201202102012021
02/01/2021


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

[p45cal]

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:
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

3517

[sandy666]

by changing en-US to en-UK


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


I suggest: en-GB