Forum Discussion
Checking if the rows have same value in different columns and merging rows -Power Query
I have a table like this:
| User | ID | start | stop |
| 100 | 1 | 01.01.19 | 01.11.19 |
| 100 | 2 | 15.02.20 | 15.03.20 |
| 100 | 3 | 15.03.20 | null |
| 101 | 4 | 15.03.20 | 20.08.20 |
| 102 | 5 | 01.01.19 | 01.05.19 |
| 102 | 6 | 01.07.19 | null |
start and stop are date columns.
So the same user can sign on multiple times. Ideally, they should not sign off and on during the same day, but an error might cause that. So I want to capture the ID's per user, where stop of one (earlier) is the same as the start of the other (latter).
Aferwards I'd also need to change the dataset: if a user have such error (sign on and off in the same day), I'd like to merge the rows, so that the previous stop date (that was the same as the start date of the latter ID) is replaced by the latter stop date. "Latter" means the latter ID, because the date itself might be null, indicating that the user has not logged off. This way I can accurately count the duration of the log-in, and the count of logins per user.
This is how it'd look after the fix:
| User | ID | start | stop |
| 100 | 1 | 01.01.19 | 01.11.19 |
| 100 | 2 | 15.02.20 | null |
| 101 | 4 | 15.03.20 | 20.08.20 |
| 102 | 5 | 01.01.19 | 01.05.19 |
| 102 | 6 | 01.07.19 | null |
As you can see, there are different number of rows per user, some users have only one ID and not all users have this error that cause sign on/off during the same day.
I'd appreciate it if you could tell me the M code/ how to do this in Power Query so that I can filter the table before loading it. If that's not possible, DAX solution counts too.
Anonymous,
Here's a DAX solution.
Create calculated columns:
Adj Stop = VAR vUser = Logon[User] VAR vID = Logon[ID] VAR vStop = Logon[stop] VAR vNextRow = FILTER ( Logon, Logon[User] = vUser && Logon[ID] = vID + 1 ) VAR vNextStart = MAXX ( vNextRow, Logon[start] ) VAR vResult = IF ( vStop = vNextStart, BLANK (), vStop ) RETURN vResult Junk Row = VAR vUser = Logon[User] VAR vID = Logon[ID] VAR vStart = Logon[start] VAR vPrevRow = FILTER ( Logon, Logon[User] = vUser && Logon[ID] = vID - 1 ) VAR vPrevStop = MAXX ( vPrevRow, Logon[stop] ) VAR vResult = IF ( vStart = vPrevStop, "Y", "N" ) RETURN vResultCreate a table visual, and filter it to exclude junk rows:
Result:
2 Replies
- DataInsights
Super User
Anonymous,
Here's a DAX solution.
Create calculated columns:
Adj Stop = VAR vUser = Logon[User] VAR vID = Logon[ID] VAR vStop = Logon[stop] VAR vNextRow = FILTER ( Logon, Logon[User] = vUser && Logon[ID] = vID + 1 ) VAR vNextStart = MAXX ( vNextRow, Logon[start] ) VAR vResult = IF ( vStop = vNextStart, BLANK (), vStop ) RETURN vResult Junk Row = VAR vUser = Logon[User] VAR vID = Logon[ID] VAR vStart = Logon[start] VAR vPrevRow = FILTER ( Logon, Logon[User] = vUser && Logon[ID] = vID - 1 ) VAR vPrevStop = MAXX ( vPrevRow, Logon[stop] ) VAR vResult = IF ( vStart = vPrevStop, "Y", "N" ) RETURN vResultCreate a table visual, and filter it to exclude junk rows:
Result: