Change Text Column to Date using Date.FromText

[Anonymous]

Hi there, 

I hope you can help me here. I 've got a column which is called document_field2 which has dates in it as text. 
e.g. 
80624

170624

Now I try to get these dates using following: 

= Table.AddColumn(#"Gefilterte Zeilen", "Date", each Date.FromText([document_field2], [Format="dMMyy"]), type date)

 
It does work for lines which has 6 digits (like 170624) but not for lines with 5 digits (like 80624). Do you know how to solve this? 

Best regards

[Anonymous]

I've found another solution. 
I added a custom column using:

=Text.PadStart(Text.From([document_field2]), 6, "0")

This will add an extra 0 in front when it has not 6 digits.  
After that the Date column is working right. 

[m_dekorte · Accepted solution]

And if instead you specify a culture, like so:

= Table.AddColumn(#"Gefilterte Zeilen", "Date", each Date.FromText([document_field2], [Culture="en-GB"]), type date)

This converted all dates without issue for me.

I hope this is helpful 

[m_dekorte]

Hi @Anonymous 

Try this instead:

= Table.AddColumn(#"Gefilterte Zeilen", "Date", each Date.FromText([document_field2], [Format="%dMMyy"]), type date)

Rick de Groot has an article that can help you, here.

I hope this is helpful.

[Anonymous]

Hi m-dekorte, 

Unfortuneately, there is no change in the result using Format="%dMMyy".
I had look on this article before and tried several things but didn't work either.