Today, we will discuss how to apply a default transformation to all columns at once in Power Query using the Table.TransformColumns function.
In our input data, the first column lists the names of employees, while the remaining columns represent the weekdays from Sunday to Saturday. Each column records the login and logout times for each employee over the course of one week. Login and logout times are formatted as "hhmm". We need to convert these text format times into actual times and calculate the worked hours by deducting the login time from the logout time.
I will demonstrate a trick using the Table.TransformColumns function to modify all columns simultaneously.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Transform = Table.TransformColumns(Table.RemoveColumns(Source,{"Name"}),{}, each Duration.TotalHours(Duration.FromText(Text.Insert(Text.End(_,4),2,":")) - Duration.FromText(Text.Insert(Text.Start(_,4),2,":")))),
Result = Table.FromRows(List.Transform(Table.ColumnNames(Transform), (f) => {f, List.Sum(Table.Column(Transform,f)) }),{"Day","Total Hours"})
in
Result
The Source step allows us to bring the table into the Power Query Editor.
During the Transform step,
we will begin by eliminating the name column, as it is not needed in our final output.
=Table.RemoveColumns(Source,{"Name"})
We can now implement transformation steps across all columns of the resulting table.
The process is straightforward: we will extract the last four digits to determine the logout time. Convert this text into a duration of hours and minutes.
Perform the same procedure to obtain the login time and subtract the login time from the logout time to calculate the worked hours.
Now, we can step into Core function,
1st argument is Table,
The input table for this function is the one from which we removed the name column.
2nd argument is TransformOperations as list,
In this context, we can provide column names and their corresponding operations in a nested list format. Since all columns will undergo the same transformations in our case, we omit that argument by using an empty list {}.
3rd argument is defaultTransformation as nullable function,
Text.End(_,4)
This will pick last characters from each cell of our entire table.
Text.Insert(Text.End(_,4),2,":")
This will place a ":" (colon) as the third character in the result of the Text. End function.
We used 2 as the position of the colon because Power Query is zero-based.
In the first cell, the output of this construction will appear as shown below.
'15:00'
Duration.FromText(Text.Insert(Text.End(_,4),2,":"))
This will help us to convert the text into proper logout duration as shown below,
0.15:0:0
0 day, 15 hrs, 0 mins, 0 secs
Similarly, we can select the first four digits and follow the same process to convert the text into the correct login duration.
0.12:0:0
By subtracting the login time from the logout time, we can determine the duration in hours, as demonstrated below.
0.3:0:0.
To convert the duration of an hour into a number, we can utilize the Duration.TotalHours function.
Currently, the result table appears as follows:
The result step in our query will handle calculating the cumulative sum for each column and formatting the table according to our needs.
Comments