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....
Pragati11's avatar
Pragati11
Super User
5 years ago

Hi AntrikshSharma ,

 

I don't agree modifying date column from TEXT to NUMBER data-type in excel, because Power BI can easily handle the date values that come as Text through a excel file.

 

Like see below raw data from excel:

Now I can transform this TExt Date column to date type by using Locale option and it gets converted quite easily:

 

I used United Sates date format "Using Locale" because I know they are in that format, and Power BI easily converts the text to date value:

 

Here my default Date format is United Kingdom, therefore dates are shown in this format.

 

Let me know if I mis-understood your response. 🙂

 

Thanks,

Pragati

 

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