cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Checking if the rows have same value in different columns and merging rows -Power Query

I have a table like this:

UserIDstartstop
100101.01.1901.11.19
100215.02.2015.03.20
100

3

15.03.20null
101

4

15.03.2020.08.20
102

5

01.01.1901.05.19
102

6

01.07.19null

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:

UserIDstartstop
100101.01.1901.11.19
100215.02.20null
101

4

15.03.2020.08.20
102

5

01.01.1901.05.19
102

6

01.07.19null

 

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

@cansut,

 

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
    vResult

 

Create a table visual, and filter it to exclude junk rows:

 

DataInsights_0-1603131563990.png

 

Result:

 

DataInsights_1-1603131584510.png

 

View solution in original post

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

@cansut,

 

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
    vResult

 

Create a table visual, and filter it to exclude junk rows:

 

DataInsights_0-1603131563990.png

 

Result:

 

DataInsights_1-1603131584510.png

 

View solution in original post

Highlighted

Thank you this worked! But I realize that I can't do what I need with these columns and have to do the same thing in Power Query. So I'd appreciate it if anyone could explain how to do it in Power Query as well. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors