Forum Discussion
Multiple conditions using cell reference in power query
Dear Friends,
The first table below is the master data and the second is the transaction data. First table contains salary slab, performance rating as exceptional, effective, compenent and ineffective. Eg. , Salary below 20700, if exceptional, will get 15% of basic, if effective 10% of basic and if competent 5% of basic as increment. The second table contains the person-wise details and has to be match this first table to the second table individually with their ratings, percentage to the basic. Could someone help me to do this through power query please?
Salary slabExceptionalEffectiveCompetentIn-effective
| 20700 | 0.15 | 0.1 | 0.05 | 0 |
| 20701 | 0.14 | 0.09 | 0.04 | 0 |
| 29001 | 0.13 | 0.08 | 0.04 | 0 |
| 46801 | 0.12 | 0.07 | 0.03 | 0 |
| 65601 | 0.1 | 0.06 | 0.03 | 0 |
| 84401 | 0.06 | 0.04 | 0.02 | 0 |
EMP_IDNamePerformance RatingBASIC
| 182 | ES | Effective | 100150 |
| 162 | MPP | Effective | 96480 |
| 21017 | JR | Effective | 88620 |
| 130 | CR | Effective | 78310 |
| 16626 | SU | Competent | 65780 |
| 134 | JD | Effective | 58060 |
| 85 | ES | Effective | 48080 |
| 129 | RP | Effective | 39880 |
| 206 | HSG | Competent | 30460 |
| 16567 | AAR | Competent | 25790 |
| 210 | BBG | Competent | 18770 |
| 16904 | RSR | Effective | 16000 |
Thanks in advance and regards.
Hi Thangavel_Raju ,
Based on your example, I think your interval starts and ends like this, right?
If so, here's the workaround.
1.Unpivot the columns.
2.Merge Table2 with Table1.
3.Expand it.
4.Add a custom column, return 1 if the filter condition is met, otherwise 0.
5.Filter out 0 and you can get the percentage matching based on the performance type and value range.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Thangavel_Raju ,
For the first table with master data, you could unpivot these columns to get the performances as a column.
Then you could merge it with the second table.
Hope it helps you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen,
Thanks a lot. I shall try and come back to you.
Regards.
Hi Stephen,
Thank you.
There is one more criteria, viz. basic. If the basic is less than 20700 and the rating is effective then the percentage of increment should be 10%; if the basic is between 29000 and 46800 and the rating is effective the increment should be 8% and so on. That is the reason I said it is multiple conditions. I hope I have made my point clear. If you could help me it would be great. I do not want this to be hardcoded since the criteria may change over time.
Thanks and Regards.
Hi Thangavel_Raju ,
Based on your example, I think your interval starts and ends like this, right?
If so, here's the workaround.
1.Unpivot the columns.
2.Merge Table2 with Table1.
3.Expand it.
4.Add a custom column, return 1 if the filter condition is met, otherwise 0.
5.Filter out 0 and you can get the percentage matching based on the performance type and value range.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let Master = Table.Buffer(Excel.CurrentWorkbook(){[Name="Master"]}[Content]), Salary = Excel.CurrentWorkbook(){[Name="Salary"]}[Content], Custom1 = Table.AddColumn(Salary,"Increment",each Record.FieldOrDefault(Table.Last(Table.RemoveLastN(Master,(x)=>x[Salary slab]>[BASIC])),[Performance Rating],0)*[BASIC]) in Custom1Hi Daniel,
Thank you so much. I shall try the solution and would come back to you.
Regards
