Forum Discussion
Anonymous
6 years agoNot applicable
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.
| 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
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
4 Replies
- jdbuchanan71Super User
Table.Group(#"Changed Type", {"User", "DateTime"}, {{"Txn", each if List.Contains( _[Txn], "Login" ) then { "Login" } else _[Txn], type list }})That is super slick!
- AnonymousNot applicable
Mariusz thank you. It works perfectly.
- AnonymousNot 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