Forum Discussion

paulj1's avatar
paulj1
Icon for Helper II rankHelper II
8 years ago
Solved

Grid Matrix Lookup and match

Hello, help me please, my brain hurts......   I have a product table, extract below, each product has a size (4.68) and a quality grade (B)   Trying to do a calculated column to do a lookup on th...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI paulj1,

     

    You can enter to query editor and use unpivot columns function to transform your table as attribute and value columns.

    Power Query Unpivot Scenarios

     

     

    Then write formula to formual your area column:

    Format = 
VAR temp =
    ROUNDDOWN ( [Area], 1 )
RETURN
    IF (
        temp IN VALUES ( Grid[Size [m2]]] ),
        temp,
        MAXX ( FILTER ( ALL ( Grid ), Grid[Size [m2]]] < temp ), Grid[Size [m2]]] )
    )

     

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI paulj1,

 

You can enter to query editor and use unpivot columns function to transform your table as attribute and value columns.

Power Query Unpivot Scenarios

 

 

Then write formula to formual your area column:

Format = 
VAR temp =
    ROUNDDOWN ( [Area], 1 )
RETURN
    IF (
        temp IN VALUES ( Grid[Size [m2]]] ),
        temp,
        MAXX ( FILTER ( ALL ( Grid ), Grid[Size [m2]]] < temp ), Grid[Size [m2]]] )
    )

 

 

Regards,

Xiaoxin Sheng

paulj1's avatar
paulj1
Icon for Helper II rankHelper II
8 years ago

Thank you Xiaoxin Sheng  :smileyhappy:

 

i had done the unpivot, and was using a complicated if statement, but your solution is better.