Forum Discussion
What's wrong with excel data model Month function malfunctioning
That could mean you have texts which looks like date, not dates (aka numbers). Please check in Format, is it
or
You said:
ok let's give it some benefit of doubt
It gave the correct date which is the same as column 1, but I want the word "January" otherwise what would be the point? So achieving a word "January" is not achieved by using the FORMAT() function as we've tried it, or something is wrong, but I already did a re-install of Office also using the SetupProd_OffScrub.exe and it didn't fix it otherwise what you're saying is I cannot get a "January" by using the FORMAT([1/1/2010],"mmmm") function? Is this accurate?
That could mean you have texts which looks like date, not dates (aka numbers). Please check in Format, is it
or
I turned off the Detect column types and headers for unstructured sources so I won't have to deal with this issue knowing I had to manually set each data type on every PQ I did.
Subodh_Tiwari_sktneer by the way this formula works:
=FORMAT(MONTH(RMDS[MonthNumber]),"mmmm")
Thank you for your contribution to solve the problem
Uh, great to know the reason was found. I don't know how do you load data to data model, but if at least one record is with text where other are numbers/dates, entire column will be considered as text. It's best practice to define explicitly data types in Power Query on final steps.
You are correct the format is text, I need to turn that auto change type so I'll never have to be unsure what the heck happened without my knowledge. Thank you for your patience sir!!