# Reconciliation previous month salary with current month salary multiple columns

Occasional Contributor

# Reconciliation previous month salary with current month salary multiple columns

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

4 Replies

# Re: Reconciliation previous month salary with current month salary multiple columns

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.

# Re: Reconciliation previous month salary with current month salary multiple columns

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

# Re: Reconciliation previous month salary with current month salary multiple columns

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.

yes