Forum Discussion

dinsyp's avatar
dinsyp
Frequent Visitor
3 years ago
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 ...
  • lbendlin's avatar
    lbendlin
    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"