Update a column in power query based on windowing function

Question

&nbsp;1)I am getting data as followsUsername Datetime APIName NewSessionFlagA 7-Sep-2023 10:00am API1 0B 7-Sep-2023 10:01am API1 1A 7-Sep-2023 10:01am API1 0A 7-Sep-2023 10:02am API1 0D 7-Sep-2023 10:02am API1 0D 7-Sep-2023 10:03am API1 1B 7-Sep-2023 10:03am API1 0C 7-Sep-2023 10:03am API1 0C 7-Sep-2023 10:04am API1 0C 7-Sep-2023 10:04am API1 0C 8-Sep-2023 10:04am API1 0C 8-Sep-2023 10:04am API1 0&nbsp;I need to update column NewSessionflag to 1 for each date for each person such that NewSessionFlag is setto 1 for the first record from that username irrespective of the APIName each daySo in above data result expected isUsername&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Datetime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;APIName&nbsp; &nbsp; NewSessionFlagA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7-Sep-2023 10:00am&nbsp; &nbsp; &nbsp; &nbsp;API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7-Sep-2023 10:01am&nbsp; &nbsp; &nbsp; &nbsp;API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7-Sep-2023 10:01am&nbsp; &nbsp; &nbsp; &nbsp;API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7-Sep-2023 10:02am&nbsp; &nbsp; &nbsp; &nbsp;API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7-Sep-2023 10:02am&nbsp; &nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7-Sep-2023 10:03am&nbsp; &nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7-Sep-2023 10:03am&nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7-Sep-2023 10:03am&nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7-Sep-2023 10:04am&nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7-Sep-2023 10:04am&nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8-Sep-2023 10:04am&nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8-Sep-2023 10:04am&nbsp; &nbsp; &nbsp; API1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;I need a power query to do the update on window defined by username and date part from the datetime field such thatfirst 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 1A dax to do the same would be fine as well if thats simpler.

vijay_a_verma · Answer

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 · Answer

Thanks i am going through your solution. Can this be modified to make it work for setting&nbsp;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 &gt;2 hours..thanks so much for your quick revert

Forum Discussion

Update a column in power query based on windowing function

2 Replies

Related content

window function

Power BI Desktop Auto Updating with Windows Updates

App not updating

Window Function for Student marks

DAX SQL window function equivalent