Forum Discussion

Anonymous

Not applicable

2 years ago

Solved

Change Text Column to Date using Date.FromText

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

m_dekorte
2 years ago
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

4 Replies

Anonymous
Not applicable
2 years ago
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
  Resident Rockstar
  2 years ago
  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
Resident Rockstar
2 years ago
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
  Not applicable
  2 years ago
  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.