Forum Discussion
Power Query code for lookup value in a range in another table and return text from that table
Hi KasperJ90 ,
According to your description, here's my solution.
1. Power Query.
Add a custom column in Prod Line table.
Table.SelectRows(Range,(x)=> x[Operation From]<=[No_] and x[Operation To] >=[No_])[Minute Type]Then expand the column, get the correct result.
2. DAX
Create a calculated column in Prod Line table.
Column = MAXX ( FILTER ( 'Range', 'Range'[Operation From] <= EARLIER ( 'Prod Line'[No_] ) && 'Range'[Operation To] >= EARLIER ( 'Prod Line'[No_] ) ), 'Range'[Minute Type] )Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi KasperJ90 ,
How about this:
The idea is to first unfold the rows in the Range table and afterwards joining it in onto the ProdLine table.
1. Create a new Custom Column in Range with the following code:
{ Number.From ( [Operation From] ) ..Number.From ( [Operation To] ) }
2. Unfold the list:
This unfolding technique is pretty useful. Here a blog post about this:
3. Merge the query into the ProdLine:
4. Expand the column Minute Range from the new Range Column:
Let me know if this solves your issue 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi tackytechtom
Thank you for this detailed answer. It works yes - but I would rather include it as "lookup" function instead of merge queries. Can you help me make custom column in prod line with a function that looks in "range" table?
