Comparing Two Excels in Power BI

You need to merge the 2 files.

Merge CurrentMonth with PreviousMonth on the ID column, using a left outer join. This will help identify new joiners and employees who have changes.

Repeat the merging step, this time merging PreviousMonth with CurrentMonth on the ID column to identify employees who left.

Use a conditional column in Power Query in the 1st Merge and the same logic for your 2nd merge :

= Table.AddColumn(#"Expanded PreviousMonthData", "Custom", each if [PreviousMonthData.ID] = null then "New Joiner" else null)

Employees who left := Table.AddColumn(#"Expanded CurrentMonth", "Custom", each if [CurrentMonth.ID] = null then "Left" else null) 

Then in your 1st merge to get employees with changes :

= Table.AddColumn(#"Added Custom", "Custom.1", each if [Type] <> [PreviousMonthData.Type] or [Dep] <> [PreviousMonthData.Dep] or [Com] <> [PreviousMonthData.Com] or [CC] <> [PreviousMonthData.CC] or [FTE] <> [PreviousMonthData.FTE] or [Last] <> [PreviousMonthData.Last] or [First] <> [PreviousMonthData.First] or [Title] <> [PreviousMonthData.Title] then "Changed" else null)

I attached the pbix file with all the steps.

Hi, Sayonip 

Considering the large dataset you're talking about, try using measure. You can try the following methods.

left employees = Var _table=EXCEPT(VALUES('Previous month data'[ID]),VALUES('Current month data'[ID]))
 RETURN
 IF(SELECTEDVALUE('Previous month data'[ID]) in _table,1,0)

new joiners = Var _table=EXCEPT(VALUES('Current month data'[ID]),VALUES('Previous month data'[ID]))
 RETURN
 IF(SELECTEDVALUE('Current month data'[ID]) in _table,1,0)

Type change = 
VAR _Prevtype=CALCULATE(MAX('Previous month data'[Type]),FILTER(ALL('Previous month data'),[ID]=SELECTEDVALUE('Current month data'[ID])))
RETURN
IF(SELECTEDVALUE('Current month data'[Type])<>_Prevtype,1,0)

Dep change = 
VAR _PrevDep=CALCULATE(MAX('Previous month data'[Dep]),FILTER(ALL('Previous month data'),[ID]=SELECTEDVALUE('Current month data'[ID])))
RETURN
IF(SELECTEDVALUE('Current month data'[Dep])<>_PrevDep,1,0)

And so on, the result is:

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.