Power Query - Search an element of column A in entire column B

Question

I want to know how many new employees were hired each month. I have a database of employee lists for every month. For this, I gave each employee a code, one with the name and date of the current month and the other with the name and date of the prior month. Hence, I created a formula to search for the code from the month before in the column from the current month; if the formula cannot find the value, it provides an error (#N/A), which denotes that the employee is new. Therefore, it has a conditional that if it finds any value it is equal to 0 and if it does not find anything (#N/A) it is 1.

Example

The problem is that the database is huge and I have it in Power Query but I don't know how to use this formula there.

Answer 1

The delta column in PowerQuery is calculated for you if you have two columns, Date and Name. None of the other columns are necessary for you.

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type","Delta", (x)=>  
    if x[Date]=Date.From(List.Min(#"Changed Type"[Date])) then 0   // results for first dates on list
    else  1-Table.RowCount(Table.SelectRows(#"Changed Type", each Date.AddDays(Date.StartOfMonth(x[Date]),-1)=[Date] and [Name]=x[Name])))
in  #"Added Custom"