Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Month column not properly formatted

Hi,

 

I have monthly data imported for 10 months from 10 excel sheets from which the Month was extracted from the file names using "Text between Delimiters", the data type of the Month column is text. The column is Month and the values are - 

Apr20, Mar20, Feb20, Jan20, Dec19, Nov19, Oct19, Sep19, Aug19, Jul19.

 

When I change the format to Date, the values from Dec19 to Jul19 become Dec20,.....,Jul20. 

 

Please help with this.

 

Thanks,

Vishy

Need Help
Show and Tell
Tips and Tricks

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Make them a date column

     

    Date = "01-" & left([Month],3) & "-" & right([Month],2)

     

    Mark that as date , then try

     

    Date new= date(year([Date])+1,month([Date]),Day([Date]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,

       

      I believe the solution suggested by you is with DAX. Is there any way to do it in the Query Editor itself before loading the data?

       

      Regards,

      Vishy

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try like

    =Text.start([date],3) & (Text.End([date],2)*1+1)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,

       

      That didn't work. Let me try giving you some more details on this column. As I mentioned, I am importing data from 10 different excel sheets. Sample file name of these excel sheets are "XYZ_Apr20", "XYZ_Mar20" and so on.

       

      I created a Month column using "Text between Delimiters" as mentioned before to just get the Month and Year so my Month column has values Apr20, Mar20 and so on. The data type of this column is Text. When I change the data type to Date, Jul19 to Dec19 becomes Jul20 to Dec20 which is incorrect.

       

      Hope this gives the required info, let me know if you have additional questions.

       

      Regards,

      Vishy 

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    I tried to reproduce your issue, but it failed.It works as expected.

    Can you share some screenshots and M query code?

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.