IF - Rows based on adjacent Rows

[Anonymous]

Hi,

I'm quite a newbie to PBI, at least regarding DAX.

Anyway, I'm trying to create a peak usage report using the pairs Login and Logout. This is a resume of my querry

Anyway, sometimes I have cases like 2 consecutive logins or 2 consecutive logouts, so before I can create the pair (login and logout) on the same row, I need to clean my dataset. I was thinking about using a new column using "IF" based on the previous row to expurge these repetitions. Like: For logins, if action column from previous rows is logout, true. Same goes for logouts with login.

I just don't know the syntax for it, can someone help me out here or maybe even give me a tip like, "so... this way is even easier".

Thank you guys in advance just for reading this.

[AllisonKennedy]

@Anonymous  Couple things here:

1) You don't need DAX, you need M: You're working in Power Query Editor in your screenshots, and you will eventually want to pivot/manipulate the data to get them on the same row, so start searching for M and you might find better answers. 

2) Does the logout repeat too or just login? If it's just login, this will work: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLTN9M3MlCwsDIwAPJ88tPTU1MUPPOUYnXQ5A2R5P1LS8AKnFAUmOI3wIiABUYE9BsTcoAJuoJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USERNAME = _t, ACTION_TIME = _t, ACTION = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"USERNAME", type text}, {"ACTION_TIME", type datetime}, {"ACTION", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Logged In", each if [ACTION] = "Logged In" then [ACTION_TIME] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Logged Out", each if [ACTION] = "Logged Out" then [ACTION_TIME] else null),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column1",{ {"USERNAME", Order.Ascending},{"ACTION_TIME", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"ACTION", "ACTION_TIME"}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"Logged In"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Logged Out] <> null))
in
#"Filtered Rows"

Otherwise see if this will help: https://www.myonlinetraininghub.com/referencing-next-row-power-query

[Anonymous]

Thank you. The logouts also repeat themselves, but with the code you wrote I might be able to replicate the part of the logins by the end.

Again, thank you so much.

I'll also give it a check on the links you sent me. Thank you for that btw.