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 acknowle...
- 6 years ago
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
6 years agoNot 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