PQ - transform text string to proper date

[sandy666]

TextTextDate

0201202102012021
02/01/2021



// Table1
let
//(CC)sandy666
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Date = Table.AddColumn(Source, "Date", each
Date.FromText
(
Text.Format("#[day]/#[month]/#[year]",
[
day = Text.Start([Text],2),
month = Text.Middle([Text],2,2),
year = Text.End([Text],4)
],
"en-GB")
),
type date)
in
Date

Let's break down the code step by step:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
This line assigns the content of the table named "Table1" in the current workbook to the variable named "Source". It retrieves the data from the table.
Date = Table.AddColumn(Source, "Date", each ...)
This line creates a new column named "Date" in the "Source" table using the "Table.AddColumn" function. The values in this new column will be calculated using the expression specified within the "each" keyword.
Date.FromText(...)
This is a function that converts a text value into a date value. It takes the following arguments:
Text.Format(...) - This function formats a text string using a specified pattern. It takes two arguments:
The pattern: "#[day]/#[month]/#[year]"
The values to replace the placeholders in the pattern. In this case, it uses the values extracted from the "Text" column:
day = Text.Start([Text],2) - This extracts the first 2 characters from the "Text" column, representing the day.
month = Text.Middle([Text],2,2) - This extracts the middle 2 characters from the "Text" column starting from the third character, representing the month.
year = Text.End([Text],4) - This extracts the last 4 characters from the "Text" column, representing the year.
"en-GB" - This specifies the locale or language (English - Great Britain) for the date format.
type date
This line specifies that the result of the previous expression should be of type "date", ensuring that the values in the "Date" column are treated as date values.
in Date
This line specifies the expression to return as the result of the entire formula, which is the "Date" column created in step 2.

Overall, this formula extracts the day, month, and year from the "Text" column, formats them into a date string using the specified pattern, and then converts the formatted string into date values. The resulting date values are stored in the newly created "Date" column of the "Table1" table.