Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How to change YYYYMMDD into date format?

Hello guys,   How can i change the '20170104' from column IDT_DTM_ID into 04-01-2017?   Kind regards,   Rega  
Need Help
  • Baskar's avatar
    Baskar
    9 years ago

    No No,

     

    u r trying to create in power Query window. so only it throws error.

     

    i was gave u DAX Query.

     

    U have to create new column in Development environment .

     

    1. Create via Modelling Tab.

     

     

     

    2. Right click on Table and choose new colum and apply the formula

     

     

    Let me know if any help

     

     

     

  • gpsuser's avatar
    gpsuser
    9 years ago

    assume:  [date] is in the following format:  yyyymmdd

     

    create a new column named [dateFormatted] and apply the formula as follows:

    • dateFormatted = Date(Left([date],4),Right(left([date],6),2),right([date],2)

    select the new column and change its type to date as follows:

     

    [dateFormatted] will now be of type date,   formatted as:   dd Mmm yyyy

gpsuser's avatar
gpsuser
Advocate I
9 years ago

assume:  [date] is in the following format:  yyyymmdd

 

create a new column named [dateFormatted] and apply the formula as follows:

  • dateFormatted = Date(Left([date],4),Right(left([date],6),2),right([date],2)

select the new column and change its type to date as follows:

 

[dateFormatted] will now be of type date,   formatted as:   dd Mmm yyyy

  • AilleryO's avatar
    AilleryO
    Memorable Member
    6 years ago

    Hi,

    I'm little late but might help others...

    The fastest and easiest solution is to use the the "Column from example" and your dates should be recognized after you type in a transformation of one of your date.

    So the folowing formula will not be needed. In case you don't like code, it is the best way.

     

    If you like to write your own code, there is a M function you can use in customn column:

    Date.From(Text.From([#"DATE AUGMENT ATION"])) if you have a numeric column

    or 

    Date.From([#"DATE AUGMENT ATION"])

    (you also have the Date.fromText function, check out differences if needed)

    Wishing you good transformation

    • Theiren's avatar
      Theiren
      Advocate I
      6 years ago

      I have a very simple way to do this in Power Query Editor (late, but might help others as AilleryO also stated in his reply).

       

      Three steps:

      1. Change the date column type to Text

      2. In case you have zero-values in your data (00000000), replace the 00000000 values with blank values, i.e. leave empty (this can be done in Transform tab --> Replace Values)

      3. Change the date column type to Date

       

      Note: If you don't need to do number 2., make a new step instead of replacing the previous one, otherwise it will skip the Text conversion and you'll end up of getting an error.

  • harshularora's avatar
    harshularora
    New Member
    3 years ago

    i have a similar issue and i keep getting an error, i tried almost everything still it shows error,