Forum Discussion

AdamPolak96's avatar
AdamPolak96
Frequent Visitor
3 years ago
Solved

Power Query Exact match or next smaller item

Hi,

 

I searching for solution to get the same result like funticion in excel xlookup,
but i want the same result like in power pivot. 
 

Below function in PL

 

 

Need Help
Tips and Tricks
  • slorin's avatar
    slorin
    3 years ago

    Hi

    Add new column

    try Rating_Table[GRADE]{List.PositionOf(Rating_Table[RATING SCALE],[RATING SCALE],1,(x,y)=>x>y)+1} otherwise "F"

    Stéphane 

2 Replies

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

    Add new column

    try Rating_Table[GRADE]{List.PositionOf(Rating_Table[RATING SCALE],[RATING SCALE],1,(x,y)=>x>y)+1} otherwise "F"

    Stéphane 

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Another possibility

    try Table.SelectRows(Rating_Table, (x) => [RATING SCALE] >= x[RATING SCALE])[GRADE]{0} otherwise "F"

     

    Stéphane