Comparing Two Excels in Power BI

[Sayonip]

Hello,

I have 2 excels with employee details for previous and current month. I need to find the employees who left, new joiners, and any employee for whom the other columns are changed except for Age as Age will change for all employees every month.

I am attaching two dummy excels. Please help me with a power query or a DAX formula to compare both the excels to create reports on a monthly basis. I have a large dataset so it is becoming difficult to compare. I tried merging current data with previous data in BI but the number of left employees are not matching with the count of employees. I have followed some videos in YoutTube (https://youtu.be/294BH6vOpcs?feature=shared) but unable to clear the issue. I also tried solutions posted in similar questions in the community but couldnt solve. Any help is appreciated.

Thanks in advance

Current month data

ID	Type	Dep	Com	CC	FTE	Last	First	Title	Age
85	P	G	A		1	Or	Ia	Marketing	43
856	P	G	A	G	1	de	Me	CFO	21
77	T	G	S	SA	0.9	Be	Ni	Local	66
331	P	E	A	G	1	Al	El	Technical	32
323	P		A		0	Bs	Er	Lead Engineer	50
319	P	G	A	SA	1	De	Al	Director	45
315	P	G	S	CO	1	It	Ge	Supervisor	46
311	P	E	A	SA	1	Le	Lo	Coordinator	25
307	P		S		0.3	Ma	Go	Coordinator	28
303	P		R		0	Ni	Go	Purchase	21
299	T	E	R	PM	1	Sc	Ro	Manager	28
295	P		R		0.7	Se	An	Engineer	26
291	P	E	R	SA	1	Za	Is	Global	28
243	T	E	R	SA	0.7	Bo	El	Local	43
307	P		S		0.6	Ma	Go	Rep	28
287	P	E	R	CO	1	Ba	Ro	Technical	37
283	P	E	R	PM	1	Fa	Io	Purchase	41
279	P		R		0	Ro	Gh	Lead Engineer	43
275	P	E	R	CO	1	Ev	Os	Director	45
271	P	E	R	PM	0.8	Si	On	Lead Engineer	47
267	P		S		0	Ce	Io	Coordinator	49
263	P		R		0	Ro	An	Coordinator	51
259	P	E	R	PM	1	De	An	Manager	53
255	P	E	R	G	0.75	Ay	Ia	Purchase	55
251	T	E	R	SA	1	Bo	Go	Manager	57
247	T	E	R	G	1	Li	No	Engineer	59
243	T	G	R	SA	0.3	Bo	El	Global	43
239	T	E	R	PM	1	Ca	Lo	Technical	63
227	T	G	D	SA	1	Sc	Rt	Director	65
223	T	G	D	TS	1	De	An	Supervisor	67
219	P	G	D	R	0.5	Ch	Ic	Coordinator	69
215	P	G	S	R	1	Et	Ni	Purchase	71
211	P	G	D	TS	0.6	Ro	Go	Manager	73
207	P	G	D	TS	1	We	Er	Engineer	75

Previous month data

ID	Type	Dep	Com	CC	FTE	Last	First	Title	Age
85	P	G	A		1	Or	Ia	Marketing	43
856	P	G	A	G	1	de	Me	CFO	21
77	P	G	A	SA	1	Be	Ni	Global	66
331	P	E	A	SA	1	Al	El	Technical	32
327	P	E	A	PUR	1	Ar	Ge	Purchase	43
323	P		A		0	Bo	Er	Lead Engineer	50
319	P	G	A	SA	1	De	Al	Director	45
315	P	E	S	CO	1	It	Ge	Supervisor	46
311	P	E	S	SA	1	Le	Lo	Coordinator	25
307	P		S		0.3	Ma	Go	Manager	28
303	P		R		0	Ni	Go	Purchase	21
299	P	E	R	PM	1	Sc	Ro	Manager	28
295	P		R		0.7	Se	An	Engineer	26
291	P	E	R	SA	1	Za	Is	Global	28
243	T	E	R	SA	0.7	Bo	El	Local	43
307	P		S		0.6	Ma	Go	Rep	28
287	P	E	R	CO	1	Ba	Ro	Technical	37
283	P	E	R	PM	1	Fa	Io	Purchase	41
279	P		R		0	Ro	Go	Lead Engineer	43
275	P	E	R	CO	1	Ev	Os	Director	45
271	P	E	R	PM	1	Su	On	Supervisor	47
267	P		R		0	Ce	Io	Coordinator	49
263	P		R		0	Ro	An	Coordinator	51
259	P	E	R	PM	1	De	An	Manager	53
255	P	E	R	G	0.75	Ay	Ia	Purchase	55
251	T	E	R	CO	1	Bo	Go	Manager	57
247	T	E	R	G	1	Li	No	Engineer	59
243	T	E	R	SA	0.3	Bo	El	Global	43
239	T	E	R	PM	1	Ca	Lo	Technical	63
235	T	E	D	G	1	Mo	Na	Purchase	44
231	T	G	D	G	0.3	Sc	Nd	Lead Engineer	53
227	T	G	D	SA	1	Sc	Rt	Director	65
223	T	G	D	TS	1	De	An	Supervisor	67
219	P	G	D	R	0.5	Ch	Ic	Coordinator	69
215	P	G	D	R	1	Ev	Ne	Purchase	71
211	P	G	D	TS	1	Ro	Go	Manager	73
207	P	G	D	CO	1	We	Er	Engineer	75

[AmiraBedh · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[AmiraBedh · Accepted solution]

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.