Forum Discussion

L70F's avatar
L70F
Icon for Helper II rankHelper 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.

     

     

L70F's avatar
L70F
Icon for Helper II rankHelper 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
    Icon for Helper II rankHelper 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