Multiconditional lookup using partial text substring

[Blackban88s]

Hello all, I need to perform the Power Query equivalent of an Index Match to identify partial text (substring) within Description column of a source table. For clarity, what I’m doing is essentially labeling thousands of rows of combined .csv bank statement transaction files with a categorization based on an partial vendor name or an account number and vendor specific remittance substring within the Description column. Here is an example:

Table1 (Source)			Table2 (LookUpTable)
[Account]	[Description]		[Account]	[Substring]	[Category]
12345	AMAZON#57982		null	AMAZON	A
12345	908745-2017		null	EXXON	B
12345	AMAZONMKTPLACE#12345		null	SHELL	B
12345	EXXONMOBIL-12345		null	STARBUC	C
12345	SHELL OIL789		78910	908745	D
78910	SHELLOIL#12345		12345	908745	E
78910	EUREST STARBUC16
78910	STARBUCKS #12345
78910	908745/2015

Repeated Splitting functions are not an option here as there are simply too many variations in the transaction descriptions to apply efficiently. I’ve overcome this particular issue in the past by writing a long series of if/then M code using Text.Contains but it gets very cumbersome after about 50 different sub-strings of text.
I’ve found a few relevant threads here and some tangentially helpful blog posts using Text.Contains, Text.Combine, and List.Select but I’m not able to pull together a solution on my own. I’ve read Ken’s book – M is for Data Monkey and even though I’m not intimidated by M Code anymore, I can’t yet write my own functions and am easily tripped up by passing the wrong data type to a M function. Can anyone help or at least point me to a helpful thread?

 

[Blackban88s]

All, this problem has essentially already been solved by MarcelBeug in the thread called Search (Text Contain) multiple values. He gave a thorough explanation to similar question, I only needed to keep re-reading his responses and tinkering with the M code to make it work for me.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Result" = Table.AddColumn(Source, "Result", (This) => Text.Combine(List.Select(LookupTable[SUBSTRING], each Text.Contains(This[Description], _)),", "))
in
    #"Result"