Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
L70F
Helper II
Helper II

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 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 noMaterial
A11905NBR, 70 IRHD
B12520SBR, 70 IRHD
C17659 
D14238NBR, 70 IRHD
A18001 
B16072KALREZ
C16072KALREZ
D17614NBR, 70 IRHD
A10479 
B10498 
C17614FKM, 70 IRHD
D10633 
A17620NBR, 50 IRHD
B17620NBR, 50 IRHD
C17638 
D10783 
A18000 
B18262 
C17670 
D17670 
A17670NBR, 70 IRHD
B18000FKM, 65 IRHD
C18000FKM, 65 IRHD
D18000FKM, 65 IRHD
A18000FKM, 65 IRHD
B22340CR, 70 IRHD
C17243 
D22340EPDM, 70 IRHD

 

Reference table

Material typeStorage period
BR1825
NR1825
IR1825
SBR1825
AU1825
EU1825
CR2555
IIR2555
HNBR2555
NBR2555
FKM3650
FEPM3650
FPM3650
Q3650
EPDM3650

 

Expected output table

 

Item noMaterialStorage period
A11905NBR, 70 IRHD1825
B12520SBR, 70 IRHD1825
C17659  
D14238NBR, 70 IRHD1825
A18001  
B16072KALREZ 
C16072KALREZ 
D17614NBR, 70 IRHD1835
A10479  
B10498  
C17614FKM, 70 IRHD3650
D10633  
A17620NBR, 50 IRHD1825
B17620NBR, 50 IRHD1825
C17638  
D10783  
A18000  
B18262  
C17670  
D17670  
A17670NBR, 70 IRHD1825
B18000FKM, 65 IRHD3650
C18000FKM, 65 IRHD3650
D18000FKM, 65 IRHD3650
A18000FKM, 65 IRHD3650
B22340CR, 70 IRHD2555
C17243  
D22340EPDM, 70 IRHD3650

Thanks in advance. 

Best regards 

Larissa 

1 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

vxinruzhumsft_0-1716539097858.png

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.

 

 

View solution in original post

4 REPLIES 4
v-xinruzhu-msft
Community Support
Community Support

Hi @L70F 

You can refer to the following steps

1.Split the material column in input table first.

vxinruzhumsft_0-1716517121361.png

Then it beccome the following.

vxinruzhumsft_1-1716517157392.png

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

vxinruzhumsft_2-1716517214310.png

3.Expand the merged columns.

vxinruzhumsft_3-1716517244809.png

 

Output

vxinruzhumsft_4-1716517257781.png

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.

 

 

 

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? 

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

vxinruzhumsft_0-1716539097858.png

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.

 

 

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

Best regards

Larissa

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors