Return a value after checking two values within the right ranges

[Simon_1981]

Hi, 

I am trying to figure out how to get the right value (i.e. Color) from a weight / volume table in a products table with power query M. The returned value (color) needs to meet both criteria, the right value from a weight range and the right value from a volume range.

Table: Numbers

Table: Products

I already experimented the following code:

Table.SelectRows(Numbers,(x)=> x[Weight from]<=[Weight] and x[Weight to] >=[Weight])[Color]

I did not find a way to combine this with the second check for the right volume.

This is my goal:

Can any of you experts help me out on that? 

Thank you very much in advance. 

Many regards,

Simon

NB: I am sorry for posting with photos instead of tables with data. I tried to post with copy and pasted tables first but my posts got rejected twice.

[Simon_1981]

Good morning Claude_Xu,

you made it look so easy. Thank you so much for your help and for your solution -that's awesome!

Many regards,

Simon

[lbendlin]

What if the weight is 5.5?  what if the volume is 0.1355 ?

[Simon_1981]

Good morning Ibendlin,

I assume then the weight or the volume has to be rounded up to the next corresponding color.

Those numbers I used in the example are not made up by me but are actual numbers of shockwatch labels.

Many regards,

Simon

[Claude_Xu · Accepted solution]

Hi Simon,

Your direction is right, and overall you can implement that by

1. add a column holding the corresponding Numbers records

2. expand that newly added column which holds records and pick the Color column

let
  Numbers = Excel.CurrentWorkbook(){[Name="Numbers"]}[Content],
  Product = Excel.CurrentWorkbook(){[Name="Product"]}[Content],
  #"Add ColorTable Col" = Table.AddColumn(Product, "ColorTable", 
    (ProdRow as record) => 
      Table.SelectRows(
        Numbers, 
        each [Weight from] <= ProdRow[Weight] and ProdRow[Weight] <= [Weight to]
          and [Volume from] <= ProdRow[Volume] and ProdRow[Volume] <= [Volume to]
      )
  ),
  #"Expand Col" = Table.ExpandTableColumn(#"Add ColorTable Col", "ColorTable", {"Color"})
in
  #"Expand Col"