Forum Discussion
Helper IIIMaking a merged table for Login-Logout
Hi,
My data is in 2 tables, one for login and the other for logout. The format is as below:
I want the resulting table to be in this format:
As you can see, I want each login to have a corresponding logout on the same row. In case a user forgets to logout (or login for that matter), that part of the row should be left blank. Users can login and logout multiple times on the same day.
I don't know where to start, any help will be greatly appreciated.
Here is one way to do this. Below are 3 M queries with similar data as an example. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below (X3). The first two are just some example tables like your data.
// call this query "login" let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEytzIwACIFR18gJ7Q4tcgQSBsaGCjF6qCos0RXZwSkjeDqjPCaZ4ypDsM8kDoTkLpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Username = _t, Param1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Event", each "Login") in #"Added Custom" //call this query "logout" let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEytzI0sDIwUHD0BXJCi1OLDIG0oYGBUqwOijqgKmNUhUZA2hSqEMiEIog2I5g2SysjTOPNQLpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Username = _t, Param1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Event", each "Logout") in #"Added Custom" //call this query what you want let Source = Table.Combine({login, logout}), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Username", Order.Ascending}, {"Date", Order.Ascending}, {"Time", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "ForPivot", each if [Event] = "Login" then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"ForPivot"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Records", each #"Filled Down"{[Index]}), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Event", "ForPivot", "Records"}), #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Event]), "Event", "Records"), #"Expanded Login" = Table.ExpandRecordColumn(#"Pivoted Column", "Login", {"Date", "Time", "Username", "Param1"}, {"Date", "Time", "Username", "Param1"}), #"Expanded Logout" = Table.ExpandRecordColumn(#"Expanded Login", "Logout", {"Time", "Param1"}, {"Time.1", "Param1.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Logout",{"ForPivot"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Time", type time}, {"Username", type text}, {"Param1", Int64.Type}, {"Time.1", type time}, {"Param1.1", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Time", "Time In"}, {"Time.1", "Time Out"}, {"Param1", "Param1 (in)"}, {"Param1.1", "Param1 (out)"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Username", "Time In", "Time Out", "Param1 (in)", "Param1 (out)"}) in #"Reordered Columns"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
6 Replies
- collinq
Super User
Hi amalm ,
There are many ways to do this. Probably one of the least technical and most manual would be to bring them in as separate queries - name one as Log In and one as Log Out. Remove the top line, make the first row headers. Then, merge the two based on User and Date. The "Log Out" title will automatically come in in front of the field names. It will look like this:
I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
v-yingjlCommunity Support
Hi amalm ,
It is difficult to achieve this directly. Because there is no logic to determine when the user “forgot to log out” after logging in, for example, why log out after user1 log in at 7 am cannot be at 4 pm but must be a null value. Of course we know he is 'Forgot to log out', but the computer has no way to judge this logic to achieve the so-called correspondence between rows when the rows of the two tables are not equal.
One approach is to manually add the index column between the two tables, as shown in the following figure, to force the row number to be equal and use merge to merge the two tables. The disadvantage is that this will be quite complicated when there is a lot of data.Merge tables based on index column:
Expand and remove unnecessary columns, adjust column position and rename columns to show the result:
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
amalmThanks for the replies.
The tables that I had provided are oversimplifications of the actual data that I have.
v-yingjl the solution you provided won't work for us, because there is some other complexity involved.
However, there is one way I can think of, to derive the logic for this requirement: The values for Param1 (for each user) will always be ascending, there is no chance for it to reduce over a period of time.
So we can know if someone has forgotten to login/logout when there are 2 of either in a sequence without the corresponding other.
In this case, how can I make the resulting table?
