Update a column in power query based on windowing function

[andy192]

1)I am getting data as follows
Username Datetime APIName NewSessionFlag
A 7-Sep-2023 10:00am API1 0
B 7-Sep-2023 10:01am API1 1

A 7-Sep-2023 10:01am API1 0
A 7-Sep-2023 10:02am API1 0

D 7-Sep-2023 10:02am API1 0

D 7-Sep-2023 10:03am API1 1

B 7-Sep-2023 10:03am API1 0
C 7-Sep-2023 10:03am API1 0
C 7-Sep-2023 10:04am API1 0
C 7-Sep-2023 10:04am API1 0
C 8-Sep-2023 10:04am API1 0
C 8-Sep-2023 10:04am API1 0

I need to update column NewSessionflag to 1 for each date for each person such that NewSessionFlag is set
to 1 for the first record from that username irrespective of the APIName each day

So in above data result expected is

Username         Datetime                         APIName    NewSessionFlag
A                      7-Sep-2023 10:00am       API1            1
B                      7-Sep-2023 10:01am       API1            1
A                      7-Sep-2023 10:01am       API1            0
A                      7-Sep-2023 10:02am       API1            0

D                     7-Sep-2023 10:02am        API1            0

D                     7-Sep-2023 10:03am        API1            1

B                       7-Sep-2023 10:03am      API1            0
C                       7-Sep-2023 10:03am      API1            1
C                       7-Sep-2023 10:04am      API1            0
C                       7-Sep-2023 10:04am      API1            0
C                       8-Sep-2023 10:04am      API1            1
C                       8-Sep-2023 10:04am      API1            0

I need a power query to do the update on window defined by username and date part from the datetime field such that
first record is updated if no NewSessionFlag=1 record already exists for that username on that particular date. Even if the second /third record from a user for a day has this flag as 1 then the first record does not need to be set to 1

A dax to do the same would be fine as well if thats simpler.

[Vijay_A_Verma]

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1w1OLdA1MlYwNLAyMEjMBYo5BngagqSUYnWilZwwFBkiKzIEK8I0yRDTJExFRpiKXMhTZIzpJkyHG2Oa5EyeIhNKFFlQrCgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Username = _t, Datetime = _t, APIName = _t, NewSessionFlag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {"Datetime", type datetime}, {"APIName", type text}, {"NewSessionFlag", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each Date.From([Datetime])),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Added Custom", {"Username", "Date"}, {{"All", each if List.Contains(_[NewSessionFlag], 1) then _ else Table.ReplaceValue(Table.AddIndexColumn(_, "Index1"), each [NewSessionFlag],each if [Index1] = 0 then 1 else [NewSessionFlag], Replacer.ReplaceValue,{"NewSessionFlag"})}})[All]),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Date", "Index1"})
in
    #"Removed Columns"

[andy192]

Thanks i am going through your solution. Can this be modified to make it work for setting NewSessionFlag to 1 if there is gap of 2 hours between users first session and 2nd session ? So potentially on any day there could be multiple NewSessionFlag set for a user if gap between sessions >2 hours..thanks so much for your quick revert