Remove same occurrence if a value is found

[Anonymous]

Hi there,

I was trying to figure out for a whole day but no result so far.

The idea is when Login txn is posted for each User, any txn that comes with the same DateTime should not be acknowledge as it is just a background noise. If there is another same timestamp but doesn't have Login txn at the same time, this should still be considered.

User	Txn	DateTime
A	Login	1/01/2020 10:00
A	View	1/01/2020 10:00 -- to remove
A	Delete	1/01/2020 10:00 -- to remove
A	View	1/01/2020 10:10 -- to keep
A	View	1/01/2020 10:10 -- to keep
A	Payment	1/01/2020 10:11
B	Login	2/01/2020 9:00
B	Delete	2/01/2020 9:00 -- to remove
B	Payment	2/01/2020 9:05

My desire outcome would be : Those highlighted in Red should be removed.

User	Txn	DateTime
A	Login	1/01/2020 10:00
A	View	1/01/2020 10:10
A	View	1/01/2020 10:10
A	Payment	1/01/2020 10:11
B	Login	2/01/2020 9:00
B	Payment	2/01/2020 9:05

Any suggestion would be appreciated. Thank you

[Anonymous]

Here's the M code that does it. Paste into Power Query's Advanced Editor and test it with your datasets.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLJT8/MA9KG+gaG+kYGRgYKhgZWBgZKsToQ+bDM1HI80i6pOaklqSTqNyRaOiCxMjc1rwRDhSFYhROS+43g8pYw652Q3YdDHmEBigJTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [User = _t, Txn = _t, DateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Txn", type text}, {"DateTime", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Keep Row?", 
        each
            if Text.Lower([Txn]) = "login" then 
                true
            else
                Table.IsEmpty(
                    Table.SelectRows(
                        #"Changed Type",
                        (r) =>
                            r[User] = [User]
                            and r[DateTime] = [DateTime]
                            and Text.Lower(r[Txn]) = "login"
                    )
                )
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Keep Row?", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([#"Keep Row?"] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Keep Row?"})
in
    #"Removed Columns"

Best

D

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the attached file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Mariusz thank you. It works perfectly. 

[jdbuchanan71]

@Mariusz 

Table.Group(#"Changed Type", {"User", "DateTime"}, {{"Txn", each if List.Contains( _[Txn], "Login" ) then { "Login" } else _[Txn], type list }})

That is super slick!