Forum Discussion

Mr_Stern's avatar
Mr_Stern
Frequent Visitor
2 years ago
Solved

date format from numbers

Hi,  I have dates that import from the data as numbers in the form of yyyymmdd.

When I transform to dates it obviously gives an error.

I've tried various ways of transforming the information into an added column but I am doing something wrong.

 

Any help?

I am looking to get dd/mm/yyyy in the end idealy, however I'm just happy to get it to any form of date.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Mr_Stern ,

     

    Thanks for the reply from rajendraongole1 .

     

    Here is the sample data I created:

    Date

    20181212

    20201008

    20210305

    20220608

    20231115

    20240520

     

    Check in Power Query that the column is formatted as Text.

     

    Add a custom column:

    Date.FromText(Text.Start([Date], 4) & "-" & Text.Middle([Date], 4, 2) & "-" & Text.End([Date], 2))

     

    Check whether the format of the custom column is Date. In this case, the Date format is mm/dd/yyyy.

     

    Exit Power Query.

     

    There are two ways to change the format of the custom column to dd/mm/yyyy.

     

    1. Change directly on the tab.

     

    1. Create a measure:
    Formatted Date = FORMAT(MAX('Table'[Custom]), "dd/mm/yyyy")

     

    The page effect is as shown below:

     

    If you have any other questions, please feel free to contact me.

     

    The pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Mr_Stern ,

     

    Thanks for the reply from rajendraongole1 .

     

    Here is the sample data I created:

    Date

    20181212

    20201008

    20210305

    20220608

    20231115

    20240520

     

    Check in Power Query that the column is formatted as Text.

     

    Add a custom column:

    Date.FromText(Text.Start([Date], 4) & "-" & Text.Middle([Date], 4, 2) & "-" & Text.End([Date], 2))

     

    Check whether the format of the custom column is Date. In this case, the Date format is mm/dd/yyyy.

     

    Exit Power Query.

     

    There are two ways to change the format of the custom column to dd/mm/yyyy.

     

    1. Change directly on the tab.

     

    1. Create a measure:
    Formatted Date = FORMAT(MAX('Table'[Custom]), "dd/mm/yyyy")

     

    The page effect is as shown below:

     

    If you have any other questions, please feel free to contact me.

     

    The pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Mr_Stern - You can create in power query editor to to get conversion

    Open Power Query Editor.

    Select your table with the OrderDate column.

    Add a custom column with the following formula:

    assuming at your end there is a date field like OrderDate

    let
    Source = Text.From([OrderDate]), // Assuming the original column is named 'OrderDate'
    Year = Text.Middle(Source, 0, 4),
    Month = Text.Middle(Source, 4, 2),
    Day = Text.Middle(Source, 6, 2),
    ParsedDate = Date.FromText(Day & "/" & Month & "/" & Year)
    in
    ParsedDate

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!