Forum Discussion
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
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
Jalil1985 Since you explicitly asked for a PQ solution,, perhaps the attached workbook can get you started.
- 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.
