How to replace text in a column based on values of next and previous row?

[CubaRJ]

Hi, There!

I am looking for a way to use Power Query to replace text in two diferent columns based on a column.

This is a report coming from HR listing daily information about punch cards. It contains the names of personnel and their time stamps and overtime code.

I have this column reason that will list wrongly "Double Day" in a specific situation as per image:

That group of Double Days when found being repated after Day-Off and Embarked needs to be changed to Embarked. The file is enormous and has over 30k lines for me to do that by hand. Also it requires to happen only when daling with the same person.

Any help is greatly appreaciated!

 

[Matt Allington]

You can use the technique I cover in this video from my training course

You need to bring the value from the next record into the same row as a new column, then write a conditional column to check for the duplicate and create in updated column with the edited data

 

[CubaRJ]

Hi, Matt.

Hi Matt! I appreciate your help"

I followed your video, and it helped to spot only the first occurrence on 2/20/2024 and 2/27, but I failed to find a way to spot the dates between them.

=if [Sanitized Name]=[Sanitized Name 1] and [Reason]="Double Day" and [Previous Situation]="Day-off" 
then "Should be Embarked" 
else if [Sanitized Name]=[Sanitized Name 1] and [Reason]="Embarked" and [Previous Situation] = "Double Day"
then "Should be Embarked" 
else "OK"

See the result:

Since these days when compared to the previous days will return "OK", I could not figure out how to change them yet

I think a loop between "Should be Embarked" would do the correction I need but I am not sure if that is possible.

Is there any fix via Power Query?

 

[CubaRJ]

I was playing a little with VBA Code to test some cases and theyare promissing, but I would be having to run the query first and the load this back to the sheet and finally running the query. Also another problem is that my checking cases are specifically to test 7 times.

I was wondering if I could write something like this Power BI.

Dim i As Long

i = Sheet1.ListObjects("tblHours").ListRows.Count

For i = 2 To i
    'Debug.Print i
    'Caso ache 1 dobra
    If Sheet1.Range("N" & i).Value = "Should be Embarked" And Sheet1.Range("N" & i).Offset(1, 0).Value = "Should be Embarked" = True Then
       Sheet1.Range("N" & i).Offset(0, 1).Value = "Embarked"
       Debug.Print "Found 1"
    End If
    
    If Sheet1.Range("N" & i).Value = "Should be Embarked" And Sheet1.Range("N" & i).Offset(2, 0).Value = "Should be Embarked" = True Then
       Sheet1.Range("N" & i).Offset(1, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(2, 1).Value = "Embarked"
       
       Debug.Print "Found 2"
    End If
    
    If Sheet1.Range("N" & i).Value = "Should be Embarked" And Sheet1.Range("N" & i).Offset(3, 0).Value = "Should be Embarked" = True Then
       Sheet1.Range("N" & i).Offset(1, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(2, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(3, 1).Value = "Embarked"
       
              Debug.Print "Found 4"
    End If
    
    If Sheet1.Range("N" & i).Value = "Should be Embarked" And Sheet1.Range("N" & i).Offset(4, 0).Value = "Should be Embarked" = True Then
       Sheet1.Range("N" & i).Offset(1, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(2, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(3, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(4, 1).Value = "Embarked"
       Debug.Print "Found 4"
    End If
    
    If Sheet1.Range("N" & i).Value = "Should be Embarked" And Sheet1.Range("N" & i).Offset(5, 0).Value = "Should be Embarked" = True Then
       Sheet1.Range("N" & i).Offset(1, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(2, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(3, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(4, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(5, 1).Value = "Embarked"
       
       Debug.Print "Found 5"
    End If
    
    If Sheet1.Range("N" & i).Value = "Should be Embarked" And Sheet1.Range("N" & i).Offset(6, 0).Value = "Should be Embarked" = True Then
       Sheet1.Range("N" & i).Offset(1, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(2, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(3, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(4, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(5, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(6, 1).Value = "Embarked"
       
       Debug.Print "Found 6"
    End If
    
    If Sheet1.Range("N" & i).Value = "Should be Embarked" And Sheet1.Range("N" & i).Offset(7, 0).Value = "Should be Embarked" = True Then
       Sheet1.Range("N" & i).Offset(1, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(2, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(3, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(4, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(5, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(6, 1).Value = "Embarked"
       Sheet1.Range("N" & i).Offset(7, 1).Value = "Embarked"
              
       Debug.Print "Found 7"
    End If
Next
    
End Sub

 

[CubaRJ]

@Matt Allington do you know if Power Query handles offset similar to VBA?

[Previous Situation].offset(1,0) = "Double Day"

Thanks.

 

[Matt Allington]

I followed your video, and it helped to spot only the first occurrence on 2/20/2024 and 2/27, but I failed to find a way to spot the dates between them.

well it looks to me like you have the required data in each row, but the logic of your if statement is off. I could be wrong because I don’t understand your logic. My advice is to load the data after you have brought the next date into the row directly into Excel. Then use excel to get the answer you expect; most people are more familiar with Excel. Once you have done that, then analyse your Excel formula and try to rewrite the logic in PQ.

 

[Matt Allington]

@Matt Allington do you know if Power Query handles offset similar to VBA?

There is no offset function. Excel has a grid layout where location is fixed and important. Hence, you can point to any cell. PQ processes rows of data, and order of processing is not important. You can sort the rows as part of the processing and you can bring a copy of the data from other rows into the active row using the method I shared.

 

[CubaRJ]

(...) My advice is to load the data after you have brought the next date into the row directly into Excel. Then use excel to get the answer you expect; most people are more familiar with Excel. Once you have done that, then analyse your Excel formula and try to rewrite the logic in PQ.

Thanks, @Matt Allington !

I followed your advice, but rather than solving this with a formula, I created a VBA script to handle it. It's functioning well; I just need to update the Query and then execute my script.

This is the code I came up with:

Sub ReplaceShouldBeEmbarked()
Dim i As Long: i = Sheet1.ListObjects("tblHoursAfterQuery").ListRows.Count
    For i = 2 To i
        'Debug.Print i
        'Caso ache 1 dobra
        If Sheet1.Range("O" & i).Value = "Should be Embarked" And Sheet1.Range("O" & i).Offset(1, 0).Value = "Should be Embarked" = True Then
           Sheet1.Range("I" & i).Value = "Embarked"
        End If
        
        If Sheet1.Range("O" & i).Value = "Should be Embarked" And Sheet1.Range("O" & i).Offset(2, 0).Value = "Should be Embarked" = True Then
           Sheet1.Range("I" & i).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(1, 0).Value = "Embarked"
        End If
        
        If Sheet1.Range("O" & i).Value = "Should be Embarked" And Sheet1.Range("O" & i).Offset(3, 0).Value = "Should be Embarked" = True Then
           Sheet1.Range("I" & i).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(1, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(2, 0).Value = "Embarked"
        End If
        
        If Sheet1.Range("O" & i).Value = "Should be Embarked" And Sheet1.Range("O" & i).Offset(4, 0).Value = "Should be Embarked" = True Then
           Sheet1.Range("I" & i).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(1, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(2, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(3, 0).Value = "Embarked"
           Debug.Print "Found 4"
        End If
        
        If Sheet1.Range("O" & i).Value = "Should be Embarked" And Sheet1.Range("O" & i).Offset(5, 0).Value = "Should be Embarked" = True Then
           Sheet1.Range("I" & i).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(1, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(2, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(3, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(4, 0).Value = "Embarked"
        End If
        
        If Sheet1.Range("O" & i).Value = "Should be Embarked" And Sheet1.Range("O" & i).Offset(6, 0).Value = "Should be Embarked" = True Then
           Sheet1.Range("I" & i).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(1, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(2, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(3, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(4, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(5, 0).Value = "Embarked"
        End If
        
        If Sheet1.Range("O" & i).Value = "Should be Embarked" And Sheet1.Range("O" & i).Offset(7, 0).Value = "Should be Embarked" = True Then
           Sheet1.Range("I" & i).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(1, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(2, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(3, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(4, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(5, 0).Value = "Embarked"
           Sheet1.Range("I" & i).Offset(6, 0).Value = "Embarked"
        End If
    Next
End Sub

I am frustrated though, as it would be convenient to have all functionalities within Power BI. It does seem odd that Microsoft would develop Power BI without including comprehensive scripting capabilities in M or DAX.