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....
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

GK600 - I am not sure I entirely follow this but when I get dates in a European format in the forums and need to convert it, I do the following:

  1. In Power Query, split by /
  2. Change all created columns to Text
  3. Concatenate them back in the order I want in a new column, [2] & "/" & [1] & "/" & [3]
  4. Convert to date
  5. Remove other columns

This is all in Power Query.

  • GK600's avatar
    GK600
    Helper II
    5 years ago

    Thanks Greg_Deckler  for your repsonse.

     

    I actually don't need date to convert into UK format. I want it to stick to US format (that's how it appears but with "text" as data type when loaded into Power BI). The moment, I change the data type to "Date", it starts to cause issues. 

     

    I anyway tried as you suggested but strangely when I changed it to "Date" as Data type, it converts it into UK format (I also tried using locale). Please see below. This is quite weird as I do have US as regional setting for this file. Not sure why its pushing it to UK format. 

     

     

    Any pointer will be helpful.

     

    regards,

    Gaurav

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      GK600 - What are your regional settings File | Options and settings | Options | GLOBAL | Regional settings and also CURRENT FILE | Regional Settings. Also check if Model language has been set.

       

      • GK600's avatar
        GK600
        Helper II
        5 years ago

        Greg_Deckler  

         

        Thanks for your quick response.

         

        Please see below. Model language shows "Use Application Language". 

         

         

        Find below the regional settings but I tried to change it to US as well but it still converts to UK date format. (I also used "Use locale" to set the date type etc. but didnt help)

         

         

        Hope this helps to review the problem.

         

         Gaurav