Forum Discussion

andy192's avatar
andy192
New Member
2 years ago

Update a column in power query based on windowing function

  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-Se...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

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's avatar
andy192
New Member
2 years ago

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