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

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

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion
      5 years ago

      GK600  Sorry mate not able to figure how to get the dates to US format, I think it all comes down to the system format, becuase it works on my system with us format but not on the one with UK format, one thing you could do is to change the date format once you load the data from the Power Query to the data model.

       

      Not sure if reducing the source file size helps in quick loading of the data, but if you can then you should load the data to a database. like SQL server.

      • GK600's avatar
        GK600
        Helper II
        5 years ago

        Thanks AntrikshSharma  for your response,

         

        I don't have sql server facility as of now. I dont know how to use that either 🙂 but I think I could learn if available and useful.

         

        I didn't know that I could simply change the date format like this in the data model so my some of the issues could be resolved with this. However, I mainly had to transform data based on this date column within Power Query. I am starting to consider different manual options though if we can't figure out. I will also raise a query with Microsoft if this is an issue as suggested by Gregg.

         

        Thansk anyways for your help. Much appreciated. 

         

        regards,

        Gaurav