Forum Discussion
How to remove duplicates between initial date and 21 days
- 3 years ago
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"
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.
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"