Populate a column based on the latest boolean value

Hi dphillips 

You can do this sort of thing by joining your table with itself on the ID column, then aggregating the joined table to get the max of the IBFlag boolean value per ID, and converting this value to a number.

Using your Google sheets source as an example, here is the M code. The important steps are highlighted in red.

I created these steps with the interface, with the #"Aggregate FinalFlag" step initially using a Count of IBFlag aggregation. Then I edited the code of that step to make the aggregation each Number.From(List.Max(_))

let
    Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/19_mi0giMiB55fGEnSjkXVqXgbxLZmrpSwb-MiIqDr78/export?format=xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Year", "Semester", "ID", "YearLevel", "IBFlag"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Year", Int64.Type}, {"Semester", Int64.Type}, {"ID", Int64.Type}, {"YearLevel", Int64.Type}, {"IBFlag", type logical}}),
    MergeWithSelfOnID = Table.NestedJoin(#"Changed Type",{"ID"},#"Changed Type",{"ID"},"JoinOnID",JoinKind.LeftOuter),
    #"Aggregate FinalFlag" = Table.AggregateTableColumn(MergeWithSelfOnID, "JoinOnID", {{"IBFlag", each Number.From(List.Max(_)), "FinalFlag"}}),
    #"FinalFlag Integer" = Table.TransformColumnTypes(#"Aggregate FinalFlag",{{"FinalFlag", Int64.Type}})
in
    #"FinalFlag Integer"

Regards,

Owen