Excel Power Query

[gheyman]

I have a table column that has a Text value in it "21.03.10". I want to create a new column that uses this as a Date. I need that new Date column to be an actual date not text Thanks

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Output = Table.AddColumn(Source, "Date", each 
        #date(Number.FromText("20" & Text.Range([TextDate],0,2)), Number.FromText(Text.Range([TextDate],3,2)),Number.FromText(Text.Range([TextDate],6,2)))),
    Type = Table.TransformColumnTypes(Output,{{"Date", type date}})
in
    Type

Book2
	A	B	C	D
1	TextDate		TextDate	Date
2	21.03.10		21.03.10	3/10/2021
3	20.04.12		20.04.12	4/12/2020
4	19.12.08		19.12.08	12/8/2019
Sheet5

 

[gheyman]

Thank you. That did exactly what I needed. Very much appreciated!

 

[RoryA]

You could also try:

Date.FromText([TextDate], "en-GB")

 

[JGordon11]

You could also try:

Date.FromText([TextDate], "en-GB")

When I test that it swaps the dd and yy. But will work if the century is prepended to the TextDate. For the example above using the Date.FromText approach:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Output = Table.AddColumn(Source, "Date", each Date.FromText("20" & [TextDate]))
in
    Output

 

[RoryA]

I interpreted 21.03.10 as 21 Mar 2010