Power Query code for lookup value in a range in another table and return text from that table

[KasperJ90]

Hi all,

I have a tabel with a range of "Minute Type". I want to add the "Minute Type" to each line in tabel Prod Line based on the No_ as a calculated column:

Tabel Range

Tabel Prod Line

So as an example I would like to add Minute Type "Cutting Minutes" to the first row with No_ 0900 and "Lamination Minuts" to the second row.

How can I do this in Power Query (not DAX)?

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tackytechtom]

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:

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#unfold-expand-create-rows-between-t...

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/

[KasperJ90]

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?