Reconciliation previous month salary with current month salary multiple columns

[Jalil1985]

Hi,

Dears,

I am facing issue on reconciliation of salary between two months in power query. Following is a sample data of two months.

	*** Emp. ID. is unique
Month	Emp ID.	Name	Basic Pay	Utility Allowance	Overtime	Conveyance  Allowance	Gross Pay
Nov-21	10490	Ali	3,830	4,356	150	2,130	10,466
Nov-21	10491	Ahmed	2,110	4,432	3,072	5,742	15,356
Nov-21	10492	Jalil	3,694	5,808	3,031	2,323	14,856
Nov-21	10493	Faisal	2,811	5,980	3,044	4,593	16,428
Month	Emp ID.	Name	Basic Pay	Utility Allowance	Overtime	Conveyance Allowance	Gross Pay
Dec-21	10490	Ali	3,830	4,356	150	2,624	10,960
Dec-21	10491	Ahmed	2,110	3,226	3,187	4,396	12,919
Dec-21	10492	Jalil	5,037	4,139	5,256	3,193	17,625
Dec-21	10493	Faisal	2,811	5,980	3,044	4,593	16,428
Dec-21	10494	XYX	2200	1000	0	1000	4,200

Here is I want to reconcile each column with previous month in return result I want to give me change/difference of every column with the name of column if any.

Example there is:

Emp ID.

10490    Change/Difference is in Conveyance Allowance is increased in Dec-21 Rs.494 only one change in this ID

Whereas, in this ID three changes

10491    Change/Difference is in Utility Allowance is decreased in Dec-21 Rs.1,206,

Overtime is increased Rs.115 & decreased

Conveyance Allowance is Rs.1,346

Net impact is in Dec-21 decreased Rs.2,437

Although the following ID is New ID in only one month.

10494: New Employee in Dec-21 Rs. 4200

If any employee salary stopped it show me that employee salary is stopped.

Later on, for each employee ID the difference of every column should be indicate in one column.

If difference is more than one column for each ID result should be in more than one column

I would be thankful to you all.

Regard

Jalil

[#2]

It was a little difficult to understand what you needed. If I am not wrong, then I think you want to show the difference in payment and where the difference occured? If yes, then all you need is a difference formula within an IF Statement. Something like:
IF(D10-D4>0,D10-D4,"") or IF(D10-D4>0,D10-D4,)

If you wanted to create separate columns when there is a difference, then you need macro for that. The macro will also need to consider cases when one employee doesn't show difference while 1 or more shows difference.

Alternately; in your place, I would use the IF Statement above for each column name and join them together using '&' function in one cell. This way I don't have to use macro. Something like this:
=IF(D10-D4<>0,"Utility: "&(D10-D4)&" - ",)&IF(E10-E4<>0,"Overtime: "&(E10-E4)&" - ")&IF(F10-F4<>0,"Conveyance: "&(F10-F4))

Using a structured or formatted table will make things easier.

[#3]

@Jalil1985 Since you explicitly asked for a PQ solution,, perhaps the attached workbook can get you started. 

[#4]

@Jalil1985 

You may query each month, add Index column to each table and unpivot other than Index columns.

Merge Prev Month and Current Month on Index and Attribute (all from current month), expand and compare each record, finally pivot on Index. Result will be like

All steps are in attached file.

[#5]

yes