Looping though Records and testing IF cases.

[AnyGuyInRio]

Hi!

This was originally posted ina nother forum and I did not have answers: Cross link to other forum, the original post. 

I am strugling to fix a problem in HR reports and a Loop would be very handy, but I want this to be done exclusively in Power BI. 
Matt Allington helped me to build something I could spot the start and the end, like in the image below. 

What I need now is a way to loop in Column test and test if it is between the first and last "Should be Embarked" and change it to "Embarked".

Using VBA I achieved this, but it is testing and changing up to to 7 days. Anybody can tell me if this is possible todo in 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

[v-xinruzhu-msft · Accepted solution]

Hi @AnyGuyInRio ,

You can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZKxCsIwEIZfJWSuXHtXq+46dXBwLB0amiJYCQgdfPselqo5SKJbwvfnIN/9TaOP3XPjhkFnGqHYA+ZY8vlc6zYT8BCBmK/wcnXT2Ctj1eluusfN9kvYTWa0it8s+UIOExwTnBK8TPBtgleJ/7wvr/ROTPOptOpTqfWLEkhNHpSOPCgFeTBqhyAqh6CKYynj0xOCcMEIwv1iD3/1i/PRfjHHMKKfts7BdXw7Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Previous Situation" = _t, Date = _t, Test = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Previous Situation", type text}, {"Date", type date}, {"Test", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=[Date],
b=Date.AddDays(a,-1),
c=Date.AddDays(a,+1),
d=List.Max(Table.SelectRows(#"Changed Type",each [Date]=b)[Test]),
e=List.Max(Table.SelectRows(#"Changed Type",each [Date]=c)[Test])
in if [Previous Situation]="Double Day" and (d="Should be Embarked" or e="Should be Embarked") then "Embarked" else if [Previous Situation]="Double Day" and [Test]<>"Should be Embarked" then null else [Test]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnyGuyInRio]

Hi, @v-xinruzhu-msft thanks a million for the help!!!

Unfortunately, when I tried your code, it returned errors:
Here is the code I used: 

let
    Source = Excel.CurrentWorkbook(){[Name="tblHrReport"]}[Content],#"Changed Type" = Table.TransformColumnTypes(Source,{
                                    {"Employee Number", Int64.Type}, 
                                    {"Name", type text}, 
                                    {"Role", type text}, 
                                    {"Department", type text}, 
                                    {"Installation", type text}, 
                                    {"Att./Absence type", Int64.Type}, 
                                    {"Att./abs. type text", type text}, 
                                    {"CC", type text}, 
                                    {"Reason", type text}, 
                                    {"Date", type datetime}, 
                                    {"Hours", Int64.Type}, 
                                    {"Installation paying", type text}}),
   
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"CC", "WBS"}}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

    #"Sort by Name and Date" = Table.Sort(#"Changed Type1",{{"Employee Number", Order.Ascending}, 
                                                            {"Date", Order.Ascending}}),
    
    #"Grouped Rows" = Table.Group(#"Sort by Name and Date", {"Employee Number","Name", "Role", "Installation", "WBS", "Reason", "Installation paying", "Date" }, 
                                                            {{"SumHours", each List.Sum([Hours]), type nullable number}}),
    
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Previous Index", 0, 1, Int64.Type),
    
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Previous Index"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Employee Number", "Reason", "Date"}, {"Added Index1.Employee Number", "Added Index1.Reason", "Added Index1.Date"}),
    
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Added Index1",{{"Added Index1.Employee Number", "Previous Employee Number", Int64.Type}, {"Added Index1.Reason", "Previous Situation", type text}, {"Added Index1.Date", "Previous date", type date}}), 
    
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index", Order.Ascending}}),
    
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Test Double Days", each if [Employee Number]= [Added Index1.Employee Number] and [Reason] = "Double Day" and [Added Index1.Reason] = "Day-off" then "Should be Embarked"
                                                        else if [Employee Number]=[Added Index1.Employee Number] and [Reason] = "Embarked" and [Added Index1.Reason] = "Double Day" then "Should be Embarked"
                                                           else if [Employee Number]=[Added Index1.Employee Number] and [Reason] = "Vacation" and [Added Index1.Reason] = "Double Day" then "OK"
                                                           else "OK"),
    
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    
    #"Renamed Columns2" = Table.RenameColumns(#"Sorted Rows1",{{"Added Index1.Reason", "Previous Situation"}}),
    
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Added Index1.Date"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns",{{"Test Double Days", "Test"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns3", "Custom", each let a=[Date],
b=Date.AddDays(a,-1),
c=Date.AddDays(a,+1),
d=List.Max(Table.SelectRows(#"Changed Type",each [Date]=b)[Test]),
e=List.Max(Table.SelectRows(#"Changed Type",each [Date]=c)[Test])
in if [Previous Situation]="Double Day" and (d="Should be Embarked" or e="Should be Embarked") then "Embarked" else if [Previous Situation]="Double Day" and [Test]<>"Should be Embarked" then null else [Test]), 

   #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"})
in
    #"Filled Down"

Once I input the code, it won't find the "Should be Embarked" in some cases, and returns errors in cases found between "Should be Embarked".

Can you help me? I have posted the file sanitized in this Link 

[MattAllington]

If you're going to post the same question in different forums, you should cross link. 

[AnyGuyInRio]

I will leave a link here to the original post, but in that post is a half solution

Cross link to other forum, the original post.