Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Thangavel_Raju
Frequent Visitor

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

207000.150.10.050
207010.140.090.040
290010.130.080.040
468010.120.070.030
656010.10.060.030
844010.060.040.020

 

EMP_IDNamePerformance RatingBASIC

182ESEffective100150
162MPPEffective96480
21017JREffective88620
130CREffective78310
16626SUCompetent65780
134JDEffective58060
85ESEffective48080
129RPEffective39880
206HSGCompetent30460
16567AARCompetent25790
210BBGCompetent18770
16904RSREffective16000

 

Thanks in advance and regards.

1 ACCEPTED SOLUTION

Hi @Thangavel_Raju ,

 

Based on your example, I think your interval starts and ends like this, right?

vstephenmsft_2-1669965883329.png

If so, here's the workaround.

1.Unpivot the columns.

vstephenmsft_3-1669968148221.png

vstephenmsft_4-1669968361545.png

2.Merge Table2 with Table1.

vstephenmsft_5-1669968380279.png

3.Expand it.

vstephenmsft_6-1669968397636.png

4.Add a custom column, return 1 if the filter condition is met, otherwise 0.

vstephenmsft_7-1669968455200.png

vstephenmsft_8-1669968473340.png

5.Filter out 0 and you can get the percentage matching based on the performance type and value range.

vstephenmsft_10-1669968486683.png

 

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.

 

 

View solution in original post

7 REPLIES 7
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1667803559878.png

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

Hi Daniel,

 

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

Regards

v-stephen-msft
Community Support
Community Support

Hi @Thangavel_Raju ,

 

For the first table with master data, you could unpivot these columns to get the performances as a column.

1.png

vstephenmsft_0-1667802273527.png

Then you could merge it with the second table.

vstephenmsft_1-1667802339507.png

 

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,

 

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?

vstephenmsft_2-1669965883329.png

If so, here's the workaround.

1.Unpivot the columns.

vstephenmsft_3-1669968148221.png

vstephenmsft_4-1669968361545.png

2.Merge Table2 with Table1.

vstephenmsft_5-1669968380279.png

3.Expand it.

vstephenmsft_6-1669968397636.png

4.Add a custom column, return 1 if the filter condition is met, otherwise 0.

vstephenmsft_7-1669968455200.png

vstephenmsft_8-1669968473340.png

5.Filter out 0 and you can get the percentage matching based on the performance type and value range.

vstephenmsft_10-1669968486683.png

 

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.

 

 

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

Hi Stephen,

 

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

Regards.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors