Create Data with New Column dependent on two rows of data

Hi Linnil,

You can try something like this. Create a new blank query, and replace everything inside with this code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbI0BxJGhvoGRvpGBkYmSrE60UpGQCFjM5C4EYq4MUi9JUjcGEXcBCjkWFBQlF+WmgJkKoAFTUGKLTANN8Om2BzuElSTLTAUxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row/Record" = _t, #"Hours Code" = _t, Date = _t]),
    ShiftHourCode = Table.FromColumns( Table.ToColumns( Source ) & {List.RemoveFirstN( Source[Hours Code] )}, Table.ColumnNames( Source ) & {"t"} ),
    Result = Table.FromRows( List.Transform( Table.ToRows( ShiftHourCode ), 
        (row) => try ( 
            if row{1} = "Approved" 
            then {row{0}, null, null} 
            else if Number.From(row{1}) is number and row{3} = "Approved" 
            then {row{0}, row{1}, "YES"} 
            else {row{0}, row{1}, "NO"} 
        ) otherwise {null, null, null} 
    ), List.FirstN( Table.ColumnNames( Source ), 2) & {"Approved"} )
in
    Result

Returns this result.

I hope this is helpful

Linnil, there are many ways:

You can find 2 different versions how to achieve expected result (v1 should be faster, but v2 is easier to understand).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbI0BxJGhvoGRvpGBkYmSrE60UpGQCFjM5C4EYq4MUi9JUjcGEXcBCjkWFBQlF+WmgJkKoAFTUGKLTANN8Om2BzuElSTLVAVK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row/Record" = _t, #"Hours Code" = _t, Date = _t]),
    // Added null row at the beginning
    v1_HelperTable = (#table(Table.ColumnNames(Source), {List.Repeat({null}, List.Count(Table.ColumnNames(Source)))}) & Source),
    v1_Ad_Approved = Table.FromColumns(Table.ToColumns(v1_HelperTable) & Table.ToColumns(Source[[Hours Code]]), Table.ColumnNames(Source) & {"Approved?"}),
    v1_ReplaceApproved = Table.ReplaceValue(v1_Ad_Approved,
     each [#"Approved?"],
     each Text.Trim([Date]),
     (x,y,z)=> if (z = null or Text.Trim(z) = "") then null else if x = "Approved" then "YES" else "NO",
     {"Approved?"} ),
    #"v1_Filtered Rows" = Table.SelectRows(v1_ReplaceApproved, each ([Date] <> null)),
    StepBackToSource = Source,
    v2_AddedIndex1 = Table.AddIndexColumn(StepBackToSource, "Index1", 1, 1, Int64.Type),
    #"v2_Added Index0" = Table.AddIndexColumn(v2_AddedIndex1, "Index0", 0, 1, Int64.Type),
    v2_MergedQueryItself = Table.NestedJoin(#"v2_Added Index0", {"Index1"}, #"v2_Added Index0", {"Index0"}, "Added Index0", JoinKind.LeftOuter),
    v2_ExpandedAddedIndex1 = Table.ExpandTableColumn(v2_MergedQueryItself, "Added Index0", {"Hours Code"}, {"Hours Code.1"}),
    v2_Ad_Approved = Table.AddColumn(v2_ExpandedAddedIndex1, "Approved?", each if [Hours Code.1] = "Approved" then "YES" else  
if [Date] = null or Text.Trim([Date]) = "" then null 
else "NO", type text),
    v2_RemovedColumns = Table.RemoveColumns(v2_Ad_Approved,{"Index1", "Index0", "Hours Code.1"})
in
    v2_RemovedColumns