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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KunalL
Frequent Visitor

Convert Data Type from Text to Date and into a Different Date Format

Hi, 

New to Power BI and trying my hand with datasets. 

Imported a CSV where I found that the date columns in MM/DD/YYYY format have been detected as Text. I would like to have it as DD/MM/YYYY. So right clicked the column, selecting Change Type > Using Locale and then selecting Data Type as Date and Locale as English (UK). Some dates did convert correctly but some gave an error.

Tried a very unelegant way after this - splitting the column into three columns for MM, DD and YYYY and then merging them to give DD/MM/YYY. Pretty sure there is a more sophisticated way to do it. 

Thanks.

1 ACCEPTED SOLUTION

Coming back to this, I realize the function I proposed is not the right one.  Date.ToText assumes your values are already Date type, but yours are text.

 

Did you say some of your values are in general format vs date?  Does that mean you input table looks like the column on the left (dates stored as integers and date formats).

mahoneypat_0-1597194205592.png

 

If so, you can use a formula like this to convert each type differently.  We may now be beyone the more elegant solution you initially requested, but I will share this anyway in case it helps.

 

= if Text.Contains([Date], "/") then Date.ToText(Date.FromText([Date]), "dd/MM/yyyy") else Date.ToText(Date.AddDays(#date(1899,12,31), Number.FromText([Date])-1), "dd/MM/yyyy")

 

The above is an added custom column, and you should be able to convert it to Date in the following step.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

19 REPLIES 19
Gatita69
New Member

Hola

Jimmy801
Community Champion
Community Champion

Hello@KunalL 

 

you can use Table.TransformColumns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDSNzIwMlCK1YlWMgSyTRFcc31DGC8WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    Trans = Table.TransformColumns(Source, {"Date",each Date.FromText(_,"en-US"), type date})
in
    Trans

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

 

amitchandak
Super User
Super User

@KunalL ,

if you date are in UK format and you want to work with those -https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

 

Check an option in data format (screenshot at last) or under formatting string for that :https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings

 

Else create a date string like (if it is detected as date )

Format([Date],"dd-mm-yy")

Format([Date],"mm-dd-yy")

 

Format option

Data Format New Rib.png

 

@amitchandak

Thanks, this is another way. But I would like to do it in Query Editor. Because I just saw a video where it was recommended that column additions be done in Query Editor first and then as any Calculated Column - please correct as per your experience.

ilton
Helper I
Helper I

Hi! @KunalL 

Can you send some of the values that failed to convert?

KunalL
Frequent Visitor

@ilton 

 

Would you like me to share the file with you?

mahoneypat
Employee
Employee

You could also try to add a custom column with Date.ToText([DateColumn], "dd/MM/yyyy").  Upper/lower case makes a difference.  And then change it to Date type.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat,

 

I tried what you suggested. I got an error that says "Expression.Error: We cannot convert the value "1/1/1997" to type Date". 

The step is shown in PBI as (may be this helps you resolve)

 

 

= Table.AddColumn(#"Promoted Headers", "Custom", each Date.ToText([date], "dd/MM/yyyy"))

 

 I also tried using mm instead of MM but it didn't work.

Coming back to this, I realize the function I proposed is not the right one.  Date.ToText assumes your values are already Date type, but yours are text.

 

Did you say some of your values are in general format vs date?  Does that mean you input table looks like the column on the left (dates stored as integers and date formats).

mahoneypat_0-1597194205592.png

 

If so, you can use a formula like this to convert each type differently.  We may now be beyone the more elegant solution you initially requested, but I will share this anyway in case it helps.

 

= if Text.Contains([Date], "/") then Date.ToText(Date.FromText([Date]), "dd/MM/yyyy") else Date.ToText(Date.AddDays(#date(1899,12,31), Number.FromText([Date])-1), "dd/MM/yyyy")

 

The above is an added custom column, and you should be able to convert it to Date in the following step.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello @mahoneypat 

Thank you for your solution.
Can you please share more detials about how to convert text like row no. 2 and 4 into date?

The integer version of dates are relative to that starting date, so for those rows (those that don't have "/"), the expression adds that many days to it (-1).

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat ,

Thanks, the solution you suggested did not work, I got the same error message. However, I may have found a solution. Not sure how, but the solution suggested by @myndworkz on page 2 here worked for me. 

I changed the Locale setting in Options from English (United Kingdom) to English (United States). Also tested it with the table where I had converted the date using my unelegant method as described in the issue description (call it table A) and on the table coming in from the original CSV with formatting issue (table B). When I made this change and refreshed,  table A fell apart while table B displayed dates corrected in DD/MM/YYYY. When I changed it back to English (United Kingdom), a refresh broke table B but table A now displayed dates in DD/MM/YYYY. 

Would you know why?

I don't change the default locale so haven't seen that behavior. Not sure why that worked, but hopefully you are able to move forward now.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


nandic
Memorable Member
Memorable Member

@KunalL ,
You need to change you regional settings on pc.
If you check bottom-right corner you will see that currently date is displayed in "us" format.
Update regional settings like this and it will work:

date local.PNG

KunalL
Frequent Visitor

@nandic,

Thanks, I did try this but this did not work.

ilton
Helper I
Helper I

Hi! @KunalL 

I had the same problem, try Date.FromText() it behaves difrently from TransformColumnType

 

Consider marking this as a solution if it solved your problem.

KunalL
Frequent Visitor

@ilton

Thanks, Idid try it but this did not work. I had seen this in one of the posts earlier but I think this is more suitable for scenarios where the column has the entire date-time value in text and this needs to be converted to pure date format?

Greg_Deckler
Super User
Super User

@KunalL - Not sure why some of your values would cause an error. In terms of what you did, it's funny because that is the exact approach that I use when people post their data to the forums that are mm/dd/yyyy and I want it in dd/mm/yyyy. I think I posted an idea to make it a button or option to convert from one to the other.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

I got an answer by looking up the error message and searching for a post for that here - I saw your response here

I saw that its the CSV file that has the issue. Some of the cells in the column are formatted General while some are Date. Those with General formatting are the ones that are shown with the Error link saying 'DataFormat.Error: We couldn't parse the input provided as a Date value. Details: 8/26/1961.' While this may be out of the scope of Power BI, I wonder if you could point me in the direction of the resolution?

Thanks.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors