Forum Discussion

Thangavel_Raju's avatar
Thangavel_Raju
Frequent Visitor
3 years ago
Solved

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 ineff...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

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's avatar
    Thangavel_Raju
    Frequent Visitor
    3 years ago

    Hi Daniel,

     

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

    Regards