ISO Date

[tomnamoli]

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

 

[mike_honey]

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.

 

[tomnamoli]

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.

Thank you, Mike. Much appreciated

 

[Ken Puls]

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:

Date.From(  Text.End(Text.From([Column1]),4)&"-"&
  Text.Range(Text.From([Column1]),2,2)&"-"&
  Text.Start(Text.From([Column1]),2)
  )

 

[tomnamoli]

Awesome. many thanks, Ken. The formula will be an exercise in itself to understand but will be well worth it.

 

[Ken Puls]

Here, this should help: http://www.excelguru.ca/blog/2014/08/20/5-very-useful-text-formulas-power-query-edition/

The only things that aren't listed in that article are Date.From() and Text.From(). Those are both used to convert from one format to another (Power Query can't use numbers in text formulas, so you have to convert them first.)