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
Power Query:
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.
Power Query:
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