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"
Please provide sanitized sample data that fully covers your issue.
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.
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"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"I've modified the Source based on what the editor cmoes up with then I load the Advanced editor.
let
Source = Excel.CurrentWorkbook(){[Name="Table61617_2"]}[Content],
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"Getting the following error. Possibly due to the Source code?
Thanks for your support.
Yes. Sorry I didn't note this.
see message 17
Marvellous. Thanks so much for your knowledge and hard work. So much appreciated.