Forum Discussion

GK600's avatar
GK600
Helper II
5 years ago

Date Format Error

Hi,   Hope someone can help me. I have looked through many forums but didn't come across this one.   My regional settings is English (United Kingdom) and Data load type detection is set to below....
AntrikshSharma's avatar
AntrikshSharma
Community Champion
5 years ago

Hello @Pragati11 ,

Agreed, but the thing is his dates are in a different format, see the apostrophe before the date, that kind of format isn't changed by using using locale. Can you create same scenario in your system and let me know if it works for you.

1.PNG

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    5 years ago

    Pragati11  If you press F2 on any date and press enter you will see that the dates change from text to date format.

     

    GK600 I have made the changes for you in the excel file and Power Query also recognizes them as date. file is attached below my signaure.

    • Pragati11's avatar
      Pragati11
      Super User
      5 years ago

      HI AntrikshSharma ,

       

      Not sure, because at my end I see them all by default as dates. But, I don't see any APOSTROPHE character that you were talking about.

       

      Thanks,

      Pragati

      • GK600's avatar
        GK600
        Helper II
        5 years ago

        Thanks a lot Pragati11  and AntrikshSharma ,

         

        Highly appreciate your responses. Managed to learn something from both.

         

        @Pragati, I am based in UK and prefer to stick to my default settings so prefer not to change those on my machine. However, for this exercise, I like to stick to US date format. What I am not able to understand is that why does it not convert it to US format when we specify by “using locale” and selecting English(US). It didn’t change for you either (as you mentioned your settings are as such). It should accept our selection of US format when we are specifically asking for it to the Power BI. Not sure why it’s not heeding to instructions 😊 any thoughts why?

         

        @Antriksh, thanks for the tip of double --. Now, there are 2 points I have.

        - I tried to do it at my end but get below error for any date after 12th of the month (its converting fine for upto 12th). I have a feeling my system is behaving weird with it. Any thoughts on that? The one you shared clearly seem to convert well.

         

         

        - I loaded your converted file into Power BI and converts it into date format smoothly. However, its back to square 1 situation because the date format is still of UK (dd/mm/yyyy) whereas I need original US format (mm/dd/yyyy). I in fact tried to convert it by using locale etc. but it’s not changing.

         

        @Both, wondering if you may have tip on another quick question. I am working file about 20-25 different files and combining them for my purpose. They come down to about 300-400 mb. Is there a way to shorten the size and then load into Power BI. I thought converting them into csv may help but that rather increases the size.

         

        Thanks once again very much. Much appreciated.

         

        Regards,

        Gaurav