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
Anonymous
Not applicable

Text to date not converting correctly

Hi All,

I am reading in CSV data where the month column is in the format mmmm yyyy, e.g. "October 2000".

I want to convert these to dates as part of the data query.

I have tried two methods.

Firstly selecting the column and then changing the Data Type from text to date using the selector in the Transform group of the Home tab of the ribbon. 

As a second alternativeI have parsed the column into a date using the Date item in the Date & Time Column group of the Transform tab of the ribbon.

In each of these cases the conversion seems to be successful and the date is converted, for example, from "May 2000" to 1/5/2000.

However, when I go to use the data in a report I find that "October 2000" and "October 2006" convert to 30/9/2000 and 30/9/2006.  All other MMMM YYYY values convert correctly.

 

I've checked the original data and undertaken the conversion numerous times and the error persists.  I can get around this by adding a column and manually creating the date, but this shouldn't be necessary.  Anyone have any idea what's going on with this?

 

Regards,

Mark

7 REPLIES 7
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

Have you tried concatenating a "1 " to the front of the text.  to get "1 October 2000".   This will convert it to the first day of each month, but may make converstion easier.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @Phil_Seamark

Thanks for your reply.

 

I tried inserting a new column made of "1 " & [Month] and then converting it to date and got the same issue.  Astoundingly, type conversion of "1 October 2000" and "1 October 2006" to date result in 30/09/2000 and 30/09/2006 respectively.

 

I went one step further and in the query inserted:

 

#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Date.FromText(Text.End([Month],4) & "-" & (if Text.Select([Month],{"A".."z"}) = "January" then "01"
else if Text.Select([Month],{"A".."z"}) = "February" then "02"
else if Text.Select([Month],{"A".."z"}) = "March" then "03"
else if Text.Select([Month],{"A".."z"}) = "April" then "04"
else if Text.Select([Month],{"A".."z"}) = "May" then "05"
else if Text.Select([Month],{"A".."z"}) = "June" then "06"
else if Text.Select([Month],{"A".."z"}) = "July" then "07"
else if Text.Select([Month],{"A".."z"}) = "August" then "08"
else if Text.Select([Month],{"A".."z"}) = "September" then "09"
else if Text.Select([Month],{"A".."z"}) = "October" then "10"
else if Text.Select([Month],{"A".."z"}) = "November" then "11"
else if Text.Select([Month],{"A".."z"}) = "December" then "12" else 0)
& "-" & "01")),

And this too resulted in the dates 2000-10-01 and 2006-10-01 producing 30/9/2000 and 30/9/2006!

 

I have also tried adding a column in DAX:

NewDate = DATE(VALUE(RIGHT(LabourForceData[Month], 4)),
SWITCH(left(LabourForceData[Month], LEN(LabourForceData[Month]) - 5),"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12),1)

 

Same error results!

Hi @Anonymous

 

This seems a bit odd.  I just created a new PBI file using "October 2000" , "May 2000" and "October 2006" and without adding anything to them, they come through to Power BI as the correct dates.

 

Here is the PBIX file.  Can you please open and see if they work for you.  What are your regional settings set to?

 

https://1drv.ms/u/s!AtDlC2rep7a-oxipjsYiuYH_zdyK

 

 

image.png

 

image.png

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Thanks again for your reply.

This is what I see when I open your file.

Capture.PNG

 

My region settings are currently showing as English (New Zealand).

I would normally have this as English (Australia) but was mucking around with another date issue (until I updated PBI this morning I could only see month/day/year date format options) and must have forgotten to change this back.

I have now changed back to English (Australia) and am still showing the same issue.

Hi @Anonymous

 

Something is screwy with your Power BI install.  Can you please try and un-install and re-install, or try a different machine to see how my PBIX file behaves.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @Phil_Seamark,

Thanks for your perseverance.

I uninstalled then reinstalled and the issue persists, however I think I may have worked out what the issue is.

 

I noticed in your file that each of the screwy 30 September dates is a Saturday, and hence 1 October is a Sunday.  Coincidence?

Anyway, I hadn't noticed before but October 2017 also screws up - 1 October 2017 is also a Sunday.  So the only three dates which screw up are 1 Oct 2000, 2006 and 2017 and they are also the only October dates in the range I'm using (1998 to 2018) where the 1st falls on a Sunday.  

So my theory is that "1 October 2000" converts to "00:00:00 1 October 2000" and this in turn converts to "23:00:00 30 September 2000" due to some "smart" logic that says this is when summer time ends in the US (its when it starts in Australia, so we would put the clocks the other way).

Sound plausible?

 

So until Microsoft fix this bug, the workaround is to set the time to the middle of the 1st.

 

What do you think?

 

 

 

 

Anonymous
Not applicable

In your pbix file query I added a step to create a date with time (2pm) and then covert to date data type:

 

 

#"Added Custom" = Table.AddColumn(Source, "DateAsDate", each DateTime.FromText(Text.End([Date],4) & "-" & (if Text.Select([Date],{"A".."z"}) = "January" then "01"
else if Text.Select([Date],{"A".."z"}) = "February" then "02"
else if Text.Select([Date],{"A".."z"}) = "March" then "03"
else if Text.Select([Date],{"A".."z"}) = "April" then "04"
else if Text.Select([Date],{"A".."z"}) = "May" then "05"
else if Text.Select([Date],{"A".."z"}) = "June" then "06"
else if Text.Select([Date],{"A".."z"}) = "July" then "07"
else if Text.Select([Date],{"A".."z"}) = "August" then "08"
else if Text.Select([Date],{"A".."z"}) = "September" then "09"
else if Text.Select([Date],{"A".."z"}) = "October" then "10"
else if Text.Select([Date],{"A".."z"}) = "November" then "11"
else if Text.Select([Date],{"A".."z"}) = "December" then "12" else 0)
& "-" & "01T14:00:00")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateAsDate", type date}}),

 

 

This resulted in the dates still scewing up.  Although I noticed that in the edit query interface all the dates are ok.

Capture.PNG

 

However if I left the time in by using 

    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateAsDate", type datetime}}),

and then just formatted is in the report as d/mm/yyyy it worked.

 

Capture.PNG

 

I guess that will get me going for the time being.

 

 

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.