Reconciliation previous month salary with current month salary multiple columns

Occasional Contributor

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
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.

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

@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

image.png

All steps are in attached file.