I have a list of employees and for some employees I have two rows when they changed from contract to permanent. I like to remove the row where the employee was a contracter.

I want to be the output

Any suggestions how I can do this in Power Query (for testing) and / or PostgreSQL (final implementation) ?

CodePudding user response：

DELETE FROM tab
WHERE  employeeid IN (SELECT employeeid
                      FROM   tab
                      GROUP  BY employeeid
                      HAVING Count(*) > 1)
       AND employeetype = 'contract'; 

Full Execution:

postgres=# create table tab(employeeid int,employeetype varchar(20));
CREATE TABLE
postgres=# insert into tab values(216,'contract'),(641,'fulltime'),(216,'fulltime'),(851,'contract');
INSERT 0 4
postgres=# select * from tab;
 employeeid | employeetype
------------ --------------
        216 | contract
        641 | fulltime
        216 | fulltime
        851 | contract
(4 rows)

postgres=# delete from Tab where employeeid in (select employeeid from tab group by employeeid having count(*)>1) and employeetype='contract';
DELETE 1
postgres=# select * from tab;
 employeeid | employeetype
------------ --------------
        641 | fulltime
        216 | fulltime
        851 | contract
(3 rows)

CodePudding user response：

And in Power Query:

• Group by ID
• Then create a custom aggregation to
  • Filter each subtable to return only "fulltime"
  • If there are no rows after filtering, return the first "Type", otherwise return full time

let
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employeeID", Int64.Type}, {"EmployeeType", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"employeeID"}, {
        {"Employee Type", (t) => 
            let 
                ft = Table.SelectRows(t, each [EmployeeType] = "fulltime")
            in 
                if Table.RowCount(ft) > 0 then "fulltime" else t[EmployeeType]{0}}
    })
in 
    #"Grouped Rows"