Forum Discussion

howie2293's avatar
howie2293
Frequent Visitor
6 years ago
Solved

Remove duplicate Rows within a timeframe of the original entry

I have idientified on my data set that there are duplicate entries being entered creates. within 5 mintues of the orginal entry. Is there a way to be able remove the duplicate rows within that is ent...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    my  shortest solution

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZhLbhRBDIavgrIG4WfZ7luwRiw5AvfHPV094x5NGRRYAIpQEumLn7/tmq9f31DdCAFC3j6+ffnx/cf3D5TfAX4G+0xA8AFgg/1X6OGa/719+3hiYuYNRjcsIFh8FGy4UIMx3qz5MEUsmIFzh01rIEHFGmFQh0kc1oyNpWBBAUsMN/QztuHFSZZMUoPJHbMoTgYrPqzhE2Yb2IwtwumCsf8eZsXJCDRtsEzf4aQKPVLCSMNwiflGt0wap4tuBWO/5fh1SnxjPZqLWbJQ/xnmZNSkRA/heABqTUnAkA4bE/OwghEwxRKLDXhizFAwHSOWMsXsSTjKPSALXLAQXSogMcXZJaZFOJy2eJlJpI1kWhs+/IEZOS2FkxjP2EgQR8HYo8XOVja3KJioLAuwY6dMLQgq5tBiMeckqxUnHZg7J/VoLt3HpFZs4HJO7tiMTdmsFGBX37puvMFZN4eaSc9pgh02x6vn7IIV9pySNZYaGL/E3DyIdIWtnPzXMX2NieJyUe3YeCnT3Mmw3AE7Zq+tXbAX1t6JxbswxPdh4zWWl0OXSZwKIMMoIyi/uBPOrTqJIcOlAM7SCQdn3W5LoGLOa3UndhSAQSkKFrTPiDVG5+RyHGXgpUibWcJzd++zJGdXwQatz5nEjrW4WyOoWK4B6zA/5mQMk/GJKyjSSecCVsywqxzH3U2vbrqM9ariTWafjKByiAqARhedjOlkxjI+XcARrT0rYMFwP58b7F5yYarYwOVRf8EYoWAEbe3mvZx/G6hao2hzeVwLO4blNhEQoq5yKmdKYvgll8JtyS/gBYtuNEws23mgls6U/Dk6levMJg0sSkgseD3BZIN5e6XKJS7x5WtnLdgnsGLD1oKVufzzbcXEteipoHXRZa6svVcGFGsItL6ad2yOPst8So0Ogce6ek9gwfLfunqJHeMhZ52MEh2StdaQ7tbi0mRIzUv1GSyYQrMR5L4RzPNavthTHGuly9wJE6zYkK5651sVNFNTMFfqMNITEyvZpHxNrk832Xh2Zt7OowwIImlzyXy2WNAF07YzCyZFd9nd0unuvrgyPKtYtxHk/pzINsRSAJI+k/f9E3BxUjG6lBwPMzdkQq6YNOeN3NdWWmP9C5hqF9uBmYuYFw1QXg7r7ZPY/EhJ9mutYKbaFUAeg8gumGPr5KPcVpvLgVonZ7nzIUxYMfRunpxY5EOsCqc9FGWu/5uToX+ORb9AJF5mkjPSTjjnq9+JykdKiY31J4g7dso0kO3Psdyx3ZZTOWPzWu79Bu4U8MDylKqYaVcAfTTXjn37CQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ACT_ENTRY_OBJID = _t, ACTION_TO = _t, datetime = _t, Id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ACT_ENTRY_OBJID", Int64.Type}, {"ACTION_TO", type text}, {"datetime", type datetime}, {"Id", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"count", each Table.RowCount(_), type number}, {"all", each Table.Group(_, {"datetime"}, {"CLOSE", (c)=> Table.First(c)},GroupKind.Local,(x,y)=>Number.From(Duration.Minutes(y[datetime]-x[datetime])>5))}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"datetime", "CLOSE"}, {"all.datetime", "all.CLOSE"}),
    #"Expanded all.CLOSE" = Table.ExpandRecordColumn(#"Expanded all", "all.CLOSE", {"ACT_ENTRY_OBJID", "ACTION_TO"}, {"all.CLOSE.ACT_ENTRY_OBJID", "all.CLOSE.ACTION_TO"})
in
    #"Expanded all.CLOSE"
edhans's avatar
edhans
Community Champion
6 years ago

Can you explain your data and show an example of what you want? You are using terms like case_ID, SSL_Event_time, and Event Identifier, but you have no columns with those names.

 
 
 
 
 
 

howie2293's avatar
howie2293
Frequent Visitor
6 years ago

Appolgies for the confusion, I had renamed the Case_ID and  SSL_Event_time to the easily refrence with with the function that Marisuz provided

 

case_ID = Id

SSL_Event_time = datetime

ACT_ENTRY_OBJID is the event identfiter 

 

Based on the logic i looking to remove event rows created when someone has duplicated their actions.

Then using the date and time. the plan is to give anylsis on time of day and weekly/monthly stats. THis part already done it was just the peice of being able to remove user actions of resending tickets and creating mutiple entries.

 

 

 

 

 

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello howie2293 

     

    try this solution

    let
	Source = #table
	(
		{"ID","datetime","ID2"},
		{
			{"1","01/01/2020","1"},	{"2","01/01/2020","1"},	{"3","01/01/2020","1"},	{"4","01/01/2020","1"},	{"5","01/01/2020","1"},	{"6","01/01/2020","1"},	{"7","01/01/2020","1"},	
			{"8","01/01/2020","2"},	{"9","01/01/2020","2"},	{"10","02/01/2020","3"},	{"11","02/01/2020","4"}
		}
	),
	TableDistinct = Table.Distinct(Source, {"datetime", "ID2"})
in
	TableDistinct

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    try this:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZhLbhRBDIavgrIG4WfZ7luwRiw5AvfHPV094x5NGRRYAIpQEumLn7/tmq9f31DdCAFC3j6+ffnx/cf3D5TfAX4G+0xA8AFgg/1X6OGa/719+3hiYuYNRjcsIFh8FGy4UIMx3qz5MEUsmIFzh01rIEHFGmFQh0kc1oyNpWBBAUsMN/QztuHFSZZMUoPJHbMoTgYrPqzhE2Yb2IwtwumCsf8eZsXJCDRtsEzf4aQKPVLCSMNwiflGt0wap4tuBWO/5fh1SnxjPZqLWbJQ/xnmZNSkRA/heABqTUnAkA4bE/OwghEwxRKLDXhizFAwHSOWMsXsSTjKPSALXLAQXSogMcXZJaZFOJy2eJlJpI1kWhs+/IEZOS2FkxjP2EgQR8HYo8XOVja3KJioLAuwY6dMLQgq5tBiMeckqxUnHZg7J/VoLt3HpFZs4HJO7tiMTdmsFGBX37puvMFZN4eaSc9pgh02x6vn7IIV9pySNZYaGL/E3DyIdIWtnPzXMX2NieJyUe3YeCnT3Mmw3AE7Zq+tXbAX1t6JxbswxPdh4zWWl0OXSZwKIMMoIyi/uBPOrTqJIcOlAM7SCQdn3W5LoGLOa3UndhSAQSkKFrTPiDVG5+RyHGXgpUibWcJzd++zJGdXwQatz5nEjrW4WyOoWK4B6zA/5mQMk/GJKyjSSecCVsywqxzH3U2vbrqM9ariTWafjKByiAqARhedjOlkxjI+XcARrT0rYMFwP58b7F5yYarYwOVRf8EYoWAEbe3mvZx/G6hao2hzeVwLO4blNhEQoq5yKmdKYvgll8JtyS/gBYtuNEws23mgls6U/Dk6levMJg0sSkgseD3BZIN5e6XKJS7x5WtnLdgnsGLD1oKVufzzbcXEteipoHXRZa6svVcGFGsItL6ad2yOPst8So0Ogce6ek9gwfLfunqJHeMhZ52MEh2StdaQ7tbi0mRIzUv1GSyYQrMR5L4RzPNavthTHGuly9wJE6zYkK5651sVNFNTMFfqMNITEyvZpHxNrk832Xh2Zt7OowwIImlzyXy2WNAF07YzCyZFd9nd0unuvrgyPKtYtxHk/pzINsRSAJI+k/f9E3BxUjG6lBwPMzdkQq6YNOeN3NdWWmP9C5hqF9uBmYuYFw1QXg7r7ZPY/EhJ9mutYKbaFUAeg8gumGPr5KPcVpvLgVonZ7nzIUxYMfRunpxY5EOsCqc9FGWu/5uToX+ORb9AJF5mkjPSTjjnq9+JykdKiY31J4g7dso0kO3Psdyx3ZZTOWPzWu79Bu4U8MDylKqYaVcAfTTXjn37CQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ACT_ENTRY_OBJID = _t, ACTION_TO = _t, datetime = _t, Id = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"datetime", "when"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"ACT_ENTRY_OBJID", Int64.Type}, {"ACTION_TO", type text}, {"when", type datetime}, {"Id", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"ALL", each _, type table [ACT_ENTRY_OBJID=number, ACTION_TO=text, datetime=datetime, Id=text]}, {"COUNT", each Table.RowCount(_), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "close", each Table.Group([ALL], {"when"}, {"CLOSE", each Table.First(_)},GroupKind.Local,(x,y)=>Number.From(Duration.Minutes(y[when]-x[when])>5))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"close","COUNT"}),
    #"Expanded close" = Table.ExpandTableColumn(#"Removed Other Columns", "close", {"when", "CLOSE"}, {"close.when", "close.CLOSE"}),
    #"Expanded close.CLOSE" = Table.ExpandRecordColumn(#"Expanded close", "close.CLOSE", {"ACT_ENTRY_OBJID", "ACTION_TO", "when", "Id"}, {"close.CLOSE.ACT_ENTRY_OBJID", "close.CLOSE.ACTION_TO", "close.CLOSE.when", "close.CLOSE.Id"})
in
    #"Expanded close.CLOSE"

     

     

    could be shorter but now I don't have enough time to be short :-).