Text "number" value to date value?

[scott_86_]

Hi,

Referencing the below image, in PQ, is it possible to transform (or create a new column) from the figures in the below images?

For example, the top text "number" string is 230831 (which represents YYMMDD). Can this be transformed to a date vale of DD-MM-YY (I'm in Australia!)?

For context, this list of numbers in the image have been taken from PDF file names of training certificates.

 

[smozgur]

In the advanced editor, use the following code to get the result below.
Change the following names in the code with your actual values.
Table name: Table1
Column name: Value

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Convert = Table.TransformColumns(Source, {"Value", each Date.ToText(Date.FromText(Text.From(_), [Format="yyMMdd"]), [Format="dd-MM-yy"]) })
in
    Convert

The Table.TransformColumns() function takes the source table, and transforms the column values by the provided column name and the column transformation function.

In the transformation function, basically, the inner function, Date.FromText(), converts the text value by using the given format, yyMMdd. The second function, Date.ToText(), transforms the calculated value to the requested format, dd-MM-yy.
The Text.From() function makes sure the source data value is actually text in case you don't have a type change step.

Value		Value
230831		31-08-23
230712		12-07-23
230327		27-03-23
230203		03-02-23
220910		10-09-22
211215		15-12-21
191031		31-10-19
190917		17-09-19
190515		15-05-19
180817		17-08-18
180122		22-01-18
130327		27-03-13
120111		11-01-12
111021		21-10-11
110719		19-07-11
101217		17-12-10
100503		03-05-10
091203		03-12-09
091203		03-12-09

Instead of using the TransformColumns() function to make the change on the actual column, you can also use Table.AddColumn() function to create a new column. However, if you won't need the original column and if you are going to delete it anyway, then this will be only an extra step. Here is the AddColumn() version. Basically the same transformation function is used to create the new column except you need to provide the column name this time.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddColumn = Table.AddColumn(Source, "Custom", each Date.ToText(Date.FromText(Text.From([Value]), [Format="yyMMdd"]), [Format="dd-MM-yy"]))
in
    AddColumn