Forum Discussion
How to remove duplicates between initial date and 21 days
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"
Thanks very much for this:-)
I've done a test with the full data set and discovered the code only gerenates a small subset of the data. The full data will expand in each month's report. Wondering how this might be achieved?
Andrew
Please provide sanitized sample data that fully covers your issue.
Thanks. Unfortunately, the current data set is 47798 rows of data with 19 columns so will be too large to paste into the forum. The row numbers will increase at least 1000 in the following month. Any other way of providing the sanitised data?
Thanks. I missed the bit about uploading data with a link. Apologies. In process of anonymising the data which might take a while so aim to post a link tomorrow. Thanks again for your help.
Apologies for the delay. Here's the link to the full set of anaymised data https://correctionsnz.sharefile.com/d-s5fd9a416deda4f49b12e324509ca082a. As I noted, the data set will expand monthly and is always in a table named "Table61617". Thanks for your help.
Here is a preliminary version. It is not very fast but I think I can improve on it.
let Source = Excel.Workbook(Binary.Buffer(File.Contents("C:\Users\xxx\Downloads\Anonymised Data 230523.xlsx")), null, true), Table61617_2_Table = Source{[Item="Table61617_2",Kind="Table"]}[Data], #"Removed Other Columns" = Table.SelectColumns(Table61617_2_Table,{"Participant ID", "Course ID", "Course Completion Date"}), #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(#"Removed Other Columns",{{"Course Completion Date", type date}})), #"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 Date]<k[Course Completion Date] and [Course Completion Date]>k[Course Completion Date]-#duration(21,0,0,0)))) in #"Added Custom"This version performs better over larger datasets.
let Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Anonymised Data 230523.xlsx"), null, true), Table61617_2_Table = Source{[Item="Table61617_2",Kind="Table"]}[Data], #"Removed Other Columns" = Table.SelectColumns(Table61617_2_Table,{"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"