Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date automatically updated with wrong dat

I am facing a strange problem with dates in my report. I am fetching data of type date from Excel sheet to create PowerBI reports. Below is the sample data with column header "Month-Yr"

 

Month-Yr
Jan-22
Jan-22
Jan-22
Feb-22
Feb-22
Feb-22
Mar-22
Mar-22
Mar-22
Apr-22
Apr-22

 

However, when I refresh the data in PowerBI desktop after 1 Jan 2023, all the dates in the above column have been updated to year 2023. 

 

Month-Yr
22-Jan-23
22-Jan-23
22-Jan-23
22-Feb-23
22-Feb-23
22-Feb-23
22-Mar-23
22-Mar-23
22-Mar-23
22-Apr-23
22-Apr-23

 

The source Excel is still showing the correct date, but somehow the whole date column is getting tranformed when I refresh the data in PowerBI. Any suggestions on how to debug/fix this issue?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    I think it might have to do with some regional settings in either PBI or Excel, although I'm not sure about how to proceed from there.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I think it might have to do with some regional settings in either PBI or Excel, although I'm not sure about how to proceed from there.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      That worked! I turned off and then turned on Data load> Time Intelligence > Auto Date/Time option and all the dates in my PowerBI reports reset to proper format. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        For future reference, it is good practice to turn off the "Auto Date/Time" option, as that can and likely will interfere with your usage of date/time values.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    I'm guessing there is an automatic conversion of the data in the data source, you can check here:

    You can copy the original table to Notepad and then copy it back (New a Sheet --> Ctrl+A --> Format all sheet as Text --> Paste the table) to ensure that all data types are text.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum