PQ - Transforming Text to Date in Power Query

**sandy666** · 02-20-2021, 10:29 AM

(question from the web)

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

**p45cal** · 02-21-2021, 11:33 PM

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

2021-02-21_183057.png