Forum Discussion

Kris88's avatar
Kris88
New Member
2 years ago

removing record based on time conditions

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?

Need Help

1 Reply

  • serpiva64's avatar
    serpiva64
    Solution Sage
    2 years ago

    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 !