Change text date to a date type

[agd50]

How can I change this column to a date? 

DDMMYYYY (Australian date)

[agd50 · Accepted solution]

I used this YouTube video - which I found it to be a great informative resource.

https://www.youtube.com/watch?v=JJFiPtmqPAg 

[AlienSx]

Table.TransformColumns(Source, {"DateText", (x) => Date.FromText(x, [Format = "ddMMyyyy"])})

[cengizhanarslan]

Since your values are text in DDMMYYYY format, Power BI won’t auto-detect them as dates. You need to explicitly parse them.

Do this in Power Query, not DAX.

1. Select the column

2. Transform → Extract → Text Range

   • Day: start 0, length 2

   • Month: start 2, length 2

   • Year: start 4, length 4

3. Add a Custom Column:

= #date(
    Number.FromText([Year]),
    Number.FromText([Month]),
    Number.FromText([Day])
)

• Set the new column’s type to Date

• Remove the original text column if needed

[sergievs]

let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA3MDcyMDJVitUBckwNzOAcQ1MDUwTH3MAEzjEyNTBGUmZoBOHEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Columna1 = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Columna1", type text}}),
Personalizado1 = Table.TransformColumns (#"Tipo cambiado",{{"Columna1", each Text.Combine ( Splitter.SplitTextByLengths({2,2,4})(_),"/")}}),
#"Tipo cambiado1" = Table.TransformColumnTypes(Personalizado1,{{"Columna1", type date}})
in
#"Tipo cambiado1"

[sergievs]

[SundarRaj]

Hi @agd50 ,

Please have a look at the code below. Let me know if this is what you required as the end product. Thanks!

Code:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WMjA1NDIyMDJVitUBcwyROQZIHANLCCcWAA==", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Dates = _t]
),
Type = Table.TransformColumnTypes(Source, {{"Dates", type text}}),
DateType = Table.AddColumn(
Type,
"Date",
each #date(
Number.From(Text.End(_[Dates], 4)),
Number.From(Text.End(Text.Start(_[Dates], 4), 2)),
Number.From(Text.Start(_[Dates], 2))
)
),
ChangeLocaleToAus = Table.TransformColumnTypes(DateType, {{"Date", type date}}, "en-AU")
in
ChangeLocaleToAus

Regards,