Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove same occurrence if a value is found

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.

 

UserTxnDateTime
ALogin1/01/2020 10:00
AView1/01/2020 10:00 -- to remove
ADelete1/01/2020 10:00 -- to remove
AView1/01/2020 10:10 -- to keep
AView1/01/2020 10:10 -- to keep
APayment1/01/2020 10:11
BLogin2/01/2020 9:00
BDelete2/01/2020 9:00 -- to remove
BPayment2/01/2020 9:05

 

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

UserTxnDateTime
ALogin1/01/2020 10:00
AView1/01/2020 10:10
AView1/01/2020 10:10
APayment1/01/2020 10:11
BLogin2/01/2020 9:00
BPayment2/01/2020 9:05

 

Any suggestion would be appreciated. Thank you

4 Replies

    • jdbuchanan71's avatar
      jdbuchanan71
      Super User

      Mariusz 

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

      That is super slick!

    • Anonymous's avatar
      Anonymous
      Not applicable

      Mariusz thank you. It works perfectly. 

  • Anonymous's avatar
    Anonymous
    Not applicable

    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