Forum Discussion
Solved
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 ...
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"
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?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZa9rt4gDIZvJfrmI8U2f2E/Wy/h9AwdsnWo1Hbo3ddgSIAASbtEivT4xRjz4o+PFwCCd8q83l6kPH8VrGBWAnT882Xff7w+3zoY4gpbxr7+BlD7Qri8f/vz8xKgA4NQBCxXZuMvA6AC40sGwVFU8HFht7JUJ78aI1rBMUY4zC8HsFb4+pX3NFqbog6axLguE3cCVupHMMhPMOT8aJ4fOHQU1w5f8CvEtbf2XCoM1YqUt9HVBQdI/tg317Or22B8NFL2B7paT/JtMGTMZmyuqyD10ZlITzdhWpaPx9DX9cqiORqmOrelZaQICtPdaNeuMWZOqfnapFX4GmnWtqdbzEnPTGp1BoTikr3V1aIrJR3q8j3VVp8bpOIu1x5RYny20oszj0gByqWeGXhPiZEuruET3X9IRAEkIzLdy1ZhCAX2vn/ff+0nSEjHbQ+mavsbG2KNngaxKYgmuI0OoMKo9MrBvqsA7pbbyuYAhEeJROxeF/yGsYzJvrkTdLdzK4xfC2nwsSvlgOTyRiocbbenm7CtwKqDOMH4qt3rCfZAzzzTM3d6zuiiQCZ1gG31asymC2LHdawCgrPYmwNNAUSXgF4igvGB3jZKDlDQ6i5XRnrwbm3B7Oj2FSA906OpHs8C6OGwEcp22rpNg6kCG+mFd4JvThyjqqesYPL7SBPGi73h8Yb284qYeTBqDQPajXANjy4Lfqj6s16JPbC5OgDvbS4HyGCqcGBzE6zuIKeULC0Z6/5DN8NmejzqnIPzcEPoH1W2wNA+KBQqXRmy+u+AS1NHUAZbviTd2TszNtWr+8xOsPaiexVHLbDpddPdea/CQjHdaN77/As=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Participant ID" = _t, #"Course ID" = _t, #"Course Completion" = _t, Decision = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Participant ID", Int64.Type}, {"Course ID", Int64.Type}, {"Course Completion", type date}, {"Decision", type text}},"en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag", (k)=> Table.RowCount( Table.SelectRows(#"Changed Type",each [Participant ID]=k[Participant ID] and [Course ID]=k[Course ID] and [Course Completion]<k[Course Completion] and [Course Completion]>k[Course Completion]-#duration(21,0,0,0)))) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".