Forum Discussion

KasperJ90's avatar
KasperJ90
Helper III
3 years ago
Solved

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

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)?

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

3 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    3 years ago

    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-two-dates

     

     

     

    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's avatar
      KasperJ90
      Helper III
      3 years ago

      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? 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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.