Forum Discussion

L70F's avatar
L70F
Helper II
2 years ago
Solved

Dynamic vlookup in Power query for a text contains value

Hi, I need help with M codes for the Power query.  I have an input table and a reference table. The problem is that the values from the reference table (Material type) are a part of the text in the ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi L70F 

    Thanks for your quick reply, based on your descirption , you can create a custom column and input the following code.

    List.Accumulate(
        List.Numbers(0,Table.RowCount(Reference)),
        null,
        (state, current) => if Text.Contains([Material],Reference[Material type]{current}) then Reference[Storage period]{current} else state)

    Output

    Best Regards!

    Yolo Zhu

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

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi L70F 

You can refer to the following steps

1.Split the material column in input table first.

Then it beccome the following.

2.Click 'Merge queries' to combine input and reference table.

3.Expand the merged columns.

 

Output

And you can refer to the attachment.

 

Best Regards!

Yolo Zhu

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

 

 

 

L70F's avatar
L70F
Helper II
2 years ago

Hi, Thank you for the advice. Unfortunately, the material type is not always at the beginning of the text to have this solution. It might also be in the middle. (Sorry, it was not shown in the example.) That is why I asked for the condition of looking if it contains. I have been trying to use List.zip or list.accumulation or list.positionof, but I donn't know how can I embed my condition of text contains? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi L70F 

    Thanks for your quick reply, based on your descirption , you can create a custom column and input the following code.

    List.Accumulate(
        List.Numbers(0,Table.RowCount(Reference)),
        null,
        (state, current) => if Text.Contains([Material],Reference[Material type]{current}) then Reference[Storage period]{current} else state)

    Output

    Best Regards!

    Yolo Zhu

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

     

     

    • L70F's avatar
      L70F
      Helper II
      2 years ago

      Thank you, Yolo, for an excellent solution. It is what I have been searching for, and it works well. 👍

      Best regards

      Larissa