ISO Date

T

tomnamoli

New member
Joined
Apr 3, 2016
Messages
3
Reaction score
0
Points
0
Txt file that I load results in DDMMYYYY in ISO format. Using Data Type under Transform results in Error. This feature on works ISO date format is in YYYYMMDD.

How may I transform this to get into the date format of dd/mm/yyyy.

Thanks,
Tom
 
I'm not aware of any single transformation that will do that, but you could achieve it in 3 steps:
1. Transform / Split Column / By Number of Characters. Specify 2 characters and set to Split: Repeatedly. This will generate 4 columns with 2 characters in each.
2. Select the resulting 4 columns in the order you want (to get from DDMMYYYY to YYYYMMDD I would select [MyDate.3] , .4 , .2 then .1), and choose Transform / Merge Columns
3. Select the resulting [Merged] column and change the Data Type to Date.
 
mike_honey said:
I'm not aware of any single transformation that will do that, but you could achieve it in 3 steps:
1. Transform / Split Column / By Number of Characters. Specify 2 characters and set to Split: Repeatedly. This will generate 4 columns with 2 characters in each.
2. Select the resulting 4 columns in the order you want (to get from DDMMYYYY to YYYYMMDD I would select [MyDate.3] , .4 , .2 then .1), and choose Transform / Merge Columns
3. Select the resulting [Merged] column and change the Data Type to Date.
Click to expand...

Thank you, Mike. Much appreciated
 
Alternately, if you want to do this all in one step, you could do the following, assuming the initial column that holds the values is called "Column1"

Go to Add Column --> Add Custom Column
Use the following formula:
Code: 
Date.From(  Text.End(Text.From([Column1]),4)&"-"&
  Text.Range(Text.From([Column1]),2,2)&"-"&
  Text.Start(Text.From([Column1]),2)
  )
 
Awesome. many thanks, Ken. The formula will be an exercise in itself to understand but will be well worth it.
 
You must log in or register to reply here.
Back
Top