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.

     

     

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