Dec 27 2021 11:05 PM
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
Dec 28 2021 12:30 AM
Dec 28 2021 01:26 AM
@Jalil1985 Since you explicitly asked for a PQ solution,, perhaps the attached workbook can get you started.
Dec 28 2021 01:27 AM
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.
Jan 02 2022 09:29 PM