Power Query - Find matching contents from multiple other tables

Question

I am attempting to use Power Query to modify a non-trivial sized amount of data. Several dimensions of data are contained in one column's (let's say, "Column 1") values, but they are not properly demarcated in any way. In this column, I wish to use formulas to do the following things:

1. with reference to various separate tables (say, "Lookup_n") each listing all possible values for a given dimension, identify whether a substring contained in a table is present in the data in Column1
2. if it is present, insert that substring into a new column specific to that dimension, and remove it from the data in Column1

Here is an example of what I would like to have to happen:

Sample Output

Answer 1

Try entering this code in Powerquery's input after constructing the queries lookup 1 (with the lookup 1 column), lookup 2 (with the lookup 2 column), and lookup 3 (with the lookup 3 column).

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lookup = Table.UnpivotOtherColumns( Table.Combine({lookup_3, lookup_2, lookup_1}),{} , "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(Source,"custom",(i)=>(Table.SelectRows(Lookup, each Text.Contains(i[Column_1],[Value])))),
Expanded = Table.ExpandTableColumn(#"Added Custom", "custom", {"Attribute", "Value"}, {"Attribute", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Column_1", type text}, {"Attribute", type text}, {"Value", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"<none>",Replacer.ReplaceValue,{"Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"<none>"})
in #"Removed Columns"