Hello,
I'm a beginner with Power Query and I need help.
I have a database like this:
I would like rows that have the same user and are within 10 minutes of the previous row to be deleted.
Is this possible?
Hi,
i have some doubt on your data example (only one date with only one user with repeated time not sorted) but if maybe this is what you need
starting from this
you can achieve this
using these steps
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZIxCsAgDEXvklloItpa186F7uL9r1Gd2oDxT4KDw0vySH4pxLK159kHciSc+czM7Xs9t1B1A+CYAa087giIaARyEI86CAKmDliyC6ARaXEHAZKtPEwX1QG4qO8WHuVhAOg8GEBEI5DDLw9GB0HA1AFLqmsaI9LiDgIkdR4MQC2qvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type time}}),
#"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type", "Merged", each [Column1] & [Column2], type datetime),
#"Sorted Rows" = Table.Sort(#"Inserted Merged Date and Time",{{"Column3", Order.Ascending}, {"Merged", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Column3", "Index"}, #"Added Index1", {"Column3", "Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Merged"}, {"Added Index1.Merged"}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Expanded Added Index1", "Subtraction", each [Merged] - [Added Index1.Merged], type duration),
#"Calculated Total Minutes" = Table.TransformColumns(#"Inserted Time Subtraction",{{"Subtraction", Duration.TotalMinutes, type number}}),
#"Filtered Rows" = Table.SelectRows(#"Calculated Total Minutes", each [Subtraction] > 10 or [Subtraction] = null),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Merged", "Index", "Index.1", "Added Index1.Merged", "Subtraction"})
in
#"Removed Columns"
You can see how it function in the attached file
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
