Welcome to the MrExcel Message Board!
This is almost the same solution that I have recently provided for another question.
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...
www.mrexcel.com
The following is the code adapted to your question. Create a blank query and use the Advanced Editor to copy and paste this code. Change the table name with yours and execute it.
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Convert = Table.TransformColumns(Source,
{"Clock In (YYYYMMDDHHMMSS)",
each DateTime.ToText(
DateTime.FromText(
Text.From(_),
[Format="yyyyMMddHHmmss"]
),
[Format="yyyy-MM-ddTHH:mm:ss.000"]
)
})
in
Convert
How does it work? Let's start from the date extraction from the source data.
The
DateTime.FromText() converts a given text value into an actual DateTime value. The yyyyMMddHHmmss format is the format that the original data is provided in your project. Note that I used HH to indicated it is in 24 hours format. By using the following function with the given format string, we create an actual DateTime value matching the source value format to be used in the next function.
Note: _ character here identifies the row value in the transformed column. We'll get to that a bit later, and how we use it with "each" keyword.
Power Query:
DateTime.FromText(Text.From(_), [Format="yyyyMMddHHmmss"]),
Since we have the DateTime value, we can now format this value into the required format - yyyy-MM-ddTHH:mm:ss.000 in this case. We are using the
DateTime.ToText() function to format the value as we need. This function is basically the reversed version of the DateTime.FromText() function. It takes the given DateTime value (that we extracted and converted to a real DateTime value in the previous step) and formats this value as a text value.
Power Query:
DateTime.ToText(
DateTime.FromText(Text.From(_), [Format="yyyyMMddHHmmss"]),
[Format="yyyy-MM-ddTHH:mm:ss.000"]
)
And finally the
Table.TransformColumns() function. The Table.TransformColumns() function transforms the table column(s) by using the provided transformation function(s). It takes minimum two parameters, the source table and the column transformation function(s) as a list.
The first parameter, source table, is easy. It is just the table identifier that identifies your table.
Rich (BB code):
Convert = Table.TransformColumns(Source, transformation_function)
The second parameter requires a bit of explanation. It is a list comprising two items. The first item is the name of the column to be transformed, and the second item is the function that will transform the values of that column, executing the logic on 'each' row. For example, consider the following function - it simply says: 'take the Source table and add 5 to each row value in the MyColumn column.'
The underscore, '_', represents the value of 'that' row when the 'each' keyword is used to iterate through rows (or any list/array).
Rich (BB code):
Convert = Table.TransformColumns(Source, {"MyColumn", each _ + 5})
Instead of adding 5 to each value, we use our own transformation function that I explained at the beginning, and here is the result. Note that I used a single-column table named as Table1. You need to change the table name in the code if it is different in your project).
| | |
| Clock In (YYYYMMDDHHMMSS) | | Clock In (YYYYMMDDHHMMSS) |
| 20230926173000 | | 2023-09-26T17:30:00.000 |
| 20230929142000 | | 2023-09-29T14:20:00.000 |
| 20231001123000 | | 2023-10-01T12:30:00.000 |
| 20231004150000 | | 2023-10-04T15:00:00.000 |
|
|---|
Additional note on the Table.TransformColumns() function's second parameter, the list of transformation(s). The reason of using the (s) in this description is pointing the fact that we can transform multiple columns by using different transformation functions at single step. Take a look at the following sample:
Rich (BB code):
Convert = Table.TransformColumns(Source, { {"MyColumn", each _ + 5}, {"MyColumn2", each _ + 10} })
As you can see, we created a single list of lists consisting of the individual column name and transformation function, and this way, each column in the provided list will be updated as required.
The entire procedure can be executed by adding and removing columns as I explained in the earlier question linked above. I recommend the method I've outlined here if you intend to learn M Language. However, if you prefer using the Power Query interface only, it's perfectly fine to achieve the same results by adding, removing, and renaming columns.
Hope this helps. Please let us know if you have any questions.
