Multiple conditions using cell reference in power query

[Thangavel_Raju]

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.

[wdx223_Daniel]

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
    Custom1

[Thangavel_Raju]

Hi Daniel,

Thank you so much.  I shall try the solution and would come back to you.

Regards

[v-stephen-msft]

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.

[Thangavel_Raju]

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.

[v-stephen-msft · Accepted solution]

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.

[Thangavel_Raju]

Thank you so much Stephen.  I am sure this will work for me.  I shall try and confirm to you.  Thanks and Regards.

[Thangavel_Raju]

Hi Stephen,

Thanks a lot.  I shall try and come back to you.

Regards.