Reverse duplicates in time series

Question

Hi dear community,&nbsp;&nbsp;I have an interesting case with removal of duplicates in time series. I can do it in Pandas, but can not find a way to do it in M.&nbsp;Below is a typical case number 1 - order id was updated. This case is easy - sort by timecode and delete duplicates (first one will be deleted)&nbsp;Id before&nbsp; after&nbsp; timecode1&nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp; &nbsp; 00:011&nbsp; &nbsp;b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; с&nbsp; &nbsp; &nbsp; &nbsp; 00:02&nbsp;Second case is much harder to crack. In this case after value is equal to initial value - I need to delete (cancel) all such orders, not only first rows.&nbsp;Id before&nbsp; after&nbsp; timecode1&nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp; &nbsp;00:011&nbsp; &nbsp;b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp; &nbsp;00:02&nbsp;&nbsp;sometimes it could be a combo of above.&nbsp;&nbsp;Id before&nbsp; after&nbsp; timecode1&nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp; &nbsp;00:011&nbsp; &nbsp;b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; c&nbsp; &nbsp; &nbsp; &nbsp;00:021&nbsp; &nbsp;с&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; 00:03&nbsp;Any ideas would be appreciated, thanks!&nbsp;It looks like the case for Pandas and Python, but maybe it can be solved by M and/or DAX.

marcelbeug · Answer

I would expect you'd require the first "before" value and the last "after" value, and remove any resulting records with the same "before" and "after" value.In the first example that would be from a to c, not just: keep the last record.&nbsp;let
    Source = Table1,
    #"Sorted Rows" = Table.Buffer(Table.Sort(Source,{"timecode", Order.Ascending})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Id"}, {{"before", each List.First([before]), type text}, {"after", each List.Last([after]), type text}, {"timecode", each List.Max([timecode]), type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [before] &lt;&gt; [after])
in
    #"Filtered Rows"

imkef · Answer

Thanks Matt,relieved to hear :)&nbsp;Theoretically, it should be fine if you simply add another step after your sorting like this:&nbsp;Sort = let
&nbsp;&nbsp;&nbsp; Source = Table1,
&nbsp;&nbsp;&nbsp; #"Sorted Rows" = Table.Sort(Source,{"timecode", Order.Ascending}),
    #"Buffered Table" = Table.Buffer(#"Sorted Rows").
...But to be on the safe side, I always proceed like Marcel and "wrap" it directly around the syntax of the sorting-step:&nbsp;Sort = let    Source = Table1,    #"Sorted Rows" = Table.Buffer(Table.Sort(Source,{"timecode", Order.Ascending})),&nbsp;

mattallington · Answer

I think you will struggle with M to do the first item. In my experience it is not possible to guarantee the removal of the correct records (e.g. Remove earliest based on date). I normally sort on date and then remove duplicates, but I discovered recently that the removal of the correct item is not guaranteed. I confirmed this with Microsoft.

Regarding the second scenario, take a look at my technique here - this may work for you.

https://m.youtube.com/watch?v=xN2IRXQ2CvI

Third one looks tough again.

Yes I think this is a job for Python or some per suitable tool.

imkef · Answer

Hi MattAllington,have you tried using Table.Buffer after the sort? That has worked for me so far but of course I would be interested to know if there is a know issue with it. So did the confirmation you got from Microsoft include the buffered version as well?Thanks.

mattallington · Answer

ImkeF wrote:So did the confirmation you got from Microsoft include the buffered version as well?Thanks.You are right Imke.&nbsp; I went back and checked and the concensus is that Table.Buffer should fix it (my bad).&nbsp; Can you advise how you would implement this?&nbsp; Do you first sort the table, and then manually wrap Table.Buffer around the source?&nbsp; or&nbsp;something else?&nbsp;&nbsp;

Forum Discussion

Reverse duplicates in time series

7 Replies

Related content

Duplicate ranking

remove duplicate value within a row

Formatted Table Reversing Causes Errors

Removing Duplicates

Reverse filtering