Forum Discussion
How to remove duplicates between initial date and 21 days
Hi People,
As a newbie to Power Query language, I looking for guidance.
I have a large table (more than 60000 entries) of training records within which some people have repeated the learning activity more than once within a period of 21 days, even on the same day. Removing duplicate entries on the same day I am OK with including using the table.buffer to keep the sorted data in memory. Figuring out the formula for the other removal is not as easy, made probably more complicated by the desire to also run the check within the 21 day period e.g. someone may have initial completed the learning activity on 1 May, then repeats it on 15 May (delete this entry), repeats again on 25 May (delete this entry because its within 21 days of the previous). Only want to retain the 1 May entry.
The data is sorted by UserID, CourseID and Completion Date and the duplication check is then looking for duplicate entries within that sort. Hope that makes sense.
Have I provided enough information?
TIA. Andrew
Wait what, are you running this in Excel?
let Source = Excel.CurrentWorkbook(), Table61617_2 = Source{[Name="Table61617_2"]}[Content], #"Removed Other Columns" = Table.SelectColumns(Table61617_2,{"Participant ID", "Course ID", "Course Completion Date"}), #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(#"Removed Other Columns",{{"Course Completion Date", type date}})), #"Grouped Rows" = Table.Group(#"Changed Type", {"Participant ID", "Course ID"}, {{"Rows", each _, type table [Course Completion Date=nullable date]}}), Check = (tbl)=> Table.AddColumn(tbl,"Flag",(k)=> Table.RowCount(Table.SelectRows(tbl,each [Course Completion Date]<k[Course Completion Date] and [Course Completion Date]>k[Course Completion Date]-#duration(21,0,0,0)))), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "FlagNew", each if Table.RowCount([Rows])=1 then Table.AddColumn([Rows],"Flag",each 0) else Check([Rows])), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Participant ID", "Course ID", "FlagNew"}), #"Expanded FlagNew" = Table.ExpandTableColumn(#"Removed Other Columns1", "FlagNew", {"Course Completion Date", "Flag"}, {"Course Completion Date", "Flag"}) in #"Expanded FlagNew"
19 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thanks. Below is a small snippet of the data with the first 3 columns being the criteria for the delete decision. The Action column contains the main reason for keeping or deleting, while the Reason for delete expands on that.
Participant IDCourse IDCourse Completion DateActionReason for Delete 00109735 239 30/05/2017 Keep 00109735 239 11/08/2017 > 21 Days 00109735 247 10/08/2017 00109735 248 08/03/2019 00110727 199 17/10/2017 Keep 00110727 199 22/07/2021 > 21 Days 00110727 201 29/11/2019 00110727 221 15/11/2017 00110727 235 06/05/2020 Keep 00110727 235 12/02/2021 > 21 Days 00107172 222 09/01/2018 Keep 00107172 222 13/12/2019 > 21 Days 00070129 201 17/01/2018 Keep 00070129 201 08/10/2019 > 21 Days 00070129 244 09/01/2018 Keep 00070129 244 19/06/2018 > 21 Days 00070129 307 10/10/2019 Keep 00070129 307 14/01/2020 > 21 Days 00093615 199 06/05/2020 00093615 201 31/05/2019 Keep 00093615 201 05/05/2020 > 21 Days 00093615 243 25/07/2017 Keep 00093615 243 27/02/2018 > 21 Days 00093615 244 26/07/2017 Keep 00093615 244 27/10/2018 > 21 Days 00109464 201 02/08/2017 Keep 00109464 201 19/12/2017 > 21 Days 00109464 237 09/05/2017 Keep 00109464 237 24/11/2017 > 21 Days 00109464 237 19/12/2017 > 21 Days 00109464 300 08/05/2018 Keep 00109464 300 10/05/2018 Delete < 21 days 00109212 235 30/06/2017 Keep 00109212 235 30/06/2017 Delete Same day 00104001 209 18/08/2017 Keep 00104001 209 27/10/2017 > 21 Days 00104001 209 07/11/2017 > 21 Days 00104001 210 18/08/2017 Keep 00104001 210 07/11/2017 > 21 Days 00098118 199 29/04/2017 Keep 00098118 199 03/07/2019 > 21 Days 00098118 201 25/06/2021 Keep 00098118 201 28/06/2021 Delete <21 days 00098118 247 25/06/2021 Keep 00098118 247 28/06/2021 Delete <21 days 00098118 257 25/06/2021 Keep 00098118 257 28/06/2021 Delete <21 days 00097548 199 05/10/2016 Keep 00097548 199 06/12/2016 > 21 Days 00097548 199 26/06/2017 > 21 Days 00097548 229 26/06/2017 Keep 00097548 229 03/11/2017 > 21 Days 00097548 230 26/06/2017 00097548 231 21/06/2017 Keep 00097548 231 26/06/2017 Delete <21 days 00097548 232 21/06/2017 Keep 00097548 232 26/06/2017 Delete <21 days 00110190 300 22/08/2018 Keep 00110190 300 23/08/2018 Delete <21 days 00110190 303 04/03/2020 00110190 307 14/02/2020 00110190 309 10/11/2020 Keep 00110190 309 15/07/2021 > 21 Days 00110190 309 15/07/2021 Delete Same day 00110975 199 30/03/2017 Keep 00110975 199 27/10/2017 > 21 Days 00110975 199 17/11/2017 > 21 Days 00110975 248 31/08/2017 Keep 00110975 248 31/08/2017 Delete Same day 00073399 199 24/05/2017 Keep 00073399 199 24/05/2017 Delete Same day 00073399 199 01/03/2019 > 21 Days 00110919 199 30/03/2017 Keep 00110919 199 16/11/2017 > 21 Days 00110134 199 03/03/2017 > 21 Days 00110134 199 03/03/2017 Delete Same day 00110134 222 22/11/2017 00110134 226 24/06/2017 Keep 00110134 226 24/06/2017 Delete Same day 00097933 206 18/04/2019 Keep 00097933 206 30/07/2020 > 21 Days Hope this works. Was getting an error that the message was too long.
Andrew
Looks like I ran into the no headers in table problem. Going to start a new thread as recommended.
if you delete the 15 May entry then the 25 May entry has nothing to compare to. Instead of deleting rows you will want to mark them.
Thanks. That is what the original VBA was doing - it created the Keep/Delete>21 Days entries. Was hoping to avoid that:-). I tried/am trying to send the data as a proper table but keep getting an error message of too many characters. Is it still useful to try to send?
