Power Query - change Text to Date

[illusionek]

Hello

I import & consolidate multiple files from the same folder using Power Query. The file name is "XXX 210618.xlsx" and I use below code to extract date from the name of the file when importing data.

Text.Replace(Text.End([Source.Name],11),".xlsx","")

My issue is that once imported 210618 is seen as text value if I change the data type to Date, I get below error. I have also tried various functions like Date.FromText but still come across the same error message.

Any suggestions?


Many thanks.

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
    210618

 

[Ron Coderre]

Try something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ConvertToTextDate = Table.AddColumn(Source, "DateText", each "20"&Text.Replace(Text.End([Name],11),".xlsx","")),
    ConvertToDate = Table.TransformColumnTypes(ConvertToTextDate,{{"DateText", type date}})
in
    ConvertToDate

Is that something you can work with?

 

[Ron Coderre]

Well....I mucked up my first response. I didn't notice that the YEAR is the last 2 digits (Not the first 2 digits)

Try something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ConvertToTextDate = Table.AddColumn(Source, "DateText", 
        each "20"
        &Text.Start(Text.Replace(Text.End([Name],11),".xlsx",""),2)
        &Text.Range(Text.Replace(Text.End([Name],11),".xlsx",""),2,2)
        &Text.End  (Text.Replace(Text.End([Name],11),".xlsx",""),2)),
    ConvertToDate = Table.TransformColumnTypes(ConvertToTextDate,{{"DateText", type date}})
in
    ConvertToDate

Better?

 

[Ron Coderre]

(Sigh) I'll go take a nap after this post....
I *still* got the order incorrect.
This is the last in a series of final solutions:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ConvertToTextDate = Table.AddColumn(Source, "DateText", 
        each "20"
        &Text.End(Text.Replace(Text.End([Name],11),".xlsx",""),2)
        &Text.Range(Text.Replace(Text.End([Name],11),".xlsx",""),2,2)
        &Text.Start(Text.Replace(Text.End([Name],11),".xlsx",""),2)
),
    ConvertToDate = Table.TransformColumnTypes(ConvertToTextDate,{{"DateText", type date}})
in
    ConvertToDate

 

[illusionek]

Worked like a dream! Many thanks.