Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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.
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.
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
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