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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors