Forum Discussion
Return a value after checking two values within the right ranges
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.
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"
4 Replies
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"What if the weight is 5.5? what if the volume is 0.1355 ?
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
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
