Forum Discussion
Multiple conditions using cell reference in power query
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.
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,
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.
Thank you so much Stephen. I am sure this will work for me. I shall try and confirm to you. Thanks and Regards.