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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors