Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Pii
Regular Visitor

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

1 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 

View solution in original post

4 REPLIES 4
Pii
Regular Visitor

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. 

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
Super User
Super User

Hi @Pii 

 

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.

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.