Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Anonymous
Please see the attached file with a solution.
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
Hi @Anonymous
Please see the attached file with a solution.
Table.Group(#"Changed Type", {"User", "DateTime"}, {{"Txn", each if List.Contains( _[Txn], "Login" ) then { "Login" } else _[Txn], type list }})
That is super slick!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |