Dynamic vlookup in Power query for a text contains value

[L70F]

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 input table (Material). I want to find if any match is valid for each Item and return the values (Storage period) from the second column in the reference table. 

Input table

Item no	Material
A11905	NBR, 70 IRHD
B12520	SBR, 70 IRHD
C17659
D14238	NBR, 70 IRHD
A18001
B16072	KALREZ
C16072	KALREZ
D17614	NBR, 70 IRHD
A10479
B10498
C17614	FKM, 70 IRHD
D10633
A17620	NBR, 50 IRHD
B17620	NBR, 50 IRHD
C17638
D10783
A18000
B18262
C17670
D17670
A17670	NBR, 70 IRHD
B18000	FKM, 65 IRHD
C18000	FKM, 65 IRHD
D18000	FKM, 65 IRHD
A18000	FKM, 65 IRHD
B22340	CR, 70 IRHD
C17243
D22340	EPDM, 70 IRHD

Reference table

Material type	Storage period
BR	1825
NR	1825
IR	1825
SBR	1825
AU	1825
EU	1825
CR	2555
IIR	2555
HNBR	2555
NBR	2555
FKM	3650
FEPM	3650
FPM	3650
Q	3650
EPDM	3650

Expected output table

Item no	Material	Storage period
A11905	NBR, 70 IRHD	1825
B12520	SBR, 70 IRHD	1825
C17659
D14238	NBR, 70 IRHD	1825
A18001
B16072	KALREZ
C16072	KALREZ
D17614	NBR, 70 IRHD	1835
A10479
B10498
C17614	FKM, 70 IRHD	3650
D10633
A17620	NBR, 50 IRHD	1825
B17620	NBR, 50 IRHD	1825
C17638
D10783
A18000
B18262
C17670
D17670
A17670	NBR, 70 IRHD	1825
B18000	FKM, 65 IRHD	3650
C18000	FKM, 65 IRHD	3650
D18000	FKM, 65 IRHD	3650
A18000	FKM, 65 IRHD	3650
B22340	CR, 70 IRHD	2555
C17243
D22340	EPDM, 70 IRHD	3650

Thanks in advance. 

Best regards 

Larissa 

[v-xinruzhu-msft]

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]

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? 

[v-xinruzhu-msft · Accepted solution]

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]

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

Best regards

Larissa