Power Query - Find matching contents from multiple other tables

Question

I have a set of data of non-trivial size that I am trying to transform in Power Query. One column's (say, "Column_1") values holds several dimensions of data that are not consistently delimited in any way. I want to apply formulas to this column to do the following:

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 happen:

Sample Output

I am fairly new to Power Query so don't really know where to begin in formulating a solution to this. I would be very interested to hear if there is an easier way to accomplish this than using the method I have described.

Thanks!

Answer 1

In powerquery, try this code for the input after creating query lookup_1 (with column name lookup_1), query lookup_2 (with column name lookup_2_ and query lookup_3 (with column name lookup_3)

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"