Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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"
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
40 | |
26 | |
22 | |
21 | |
16 |