Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Convert Date format: from Text American format to date

Hi Team, quick question for you: I have a dataset in .txt format. When I upload the query everything comes nice and tidy out of those fields that should be coded as Date. So they are kept as text or whole number: How do I get from this 10719 to this 01/07/19. I would like to work this out at query level as i have several columns that should be coded as date. thanks thanks
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Alberto,

    this problem happens because the windows regional setting is not set to english (US).

    You can change that or you can split column by delimiter "/" and rename the columns as below

     

    Then you can create a custom column putting the day before the month:

    DateTime=[Day]&"/"&[Month]&"/"&[Year]

    I hope you can solve it now!

     

    Ciao

    Chiara

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous;

     

    in Power Query add Custom Column and use below formula:

    =Text.Start([Date],Text.Length([Date])-4)&"/"& Text.Start(Text.End([Date],4),2)&"/"&Text.End(Text.End([Date],2),4)

    where [Date] is the column you whant to change. After that change type of new column in Date Time.

     

    Best Regards

     

    Chiara

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      hi Chiara, it worked almost perfectly, just sometimes it comes up with this message: DataFormat.Error: We couldn't parse the input provided as a Date value. Details: 12/21/18 but the cell where data is has 122118. Do you know why? thanks Alberto
      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Anonymous,

         

        By my tests with the suggestion of Chiara should be helpful and I didn't get any error.

         

        In addition, you could try another way in query editor.

         

        You could split your column like below.

         

        Then create a custom column.

         

        =[Month]&"/"&[Day]&"/"&[Year]

        Here is the result.

         

         

        More details, please refer to the APPLIED STEPS in Query Editor in my test pbix file which has been attached.

         

        Best  Regards,

        Cherry