Merge Table 1 Column A to Table 2 Column A,B or C

[PHO]

Is it possible to make a left outer join.
I have a value in Table 1 Column A, and this Value can appear in table 2 Column A,B or C.
If this happens I want to merge them.

It was a comma  sepereated value in Table 2 which I had splitted.

________________________________________________________
So I want to find out whether a product group from Table 1 is contained in Table 2, in which more than one product group is grouped. Perhaps a "contains merge" would also work for the grouped non splitted Column.
I wasn't able to do this with fuzzy matching every time something was missing.

[PHO]

Thanks for the answer @dufoq3 

, I hope the example is clear enough.
I am searching for the Model category in Table 1 in Table 2, the result is shown in Result.

The file (or is there a better way to share? xlsx xls and zip aren't allowed to upload here):
https://file.io/l6LhnmE4Nxqr

[dufoq3]

[AlienSx]

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    dict = Record.FromList(Table1[Model], Table1[Category]),
    trn = Table.TransformColumns(Table2, {"Category", Splitter.SplitTextByDelimiter(",")}),
    expand = Table.ExpandListColumn(trn, "Category"),
    add_model = Table.AddColumn(expand, "Model", (x) => Record.FieldOrDefault(dict, x[Category])),
    filter = Table.SelectRows(add_model, each ([Model] <> null))
in
    filter

[PHO]

I will try it with your suggestion.
And here is another hopefully working sharing link
https://filetransfer.io/data-package/M35pGd0u#link

Or based on your second Link:
table 1

Model	Category
Model1	A
Model2	B
Model3	C

table2

Data 1	Data 2	Category
1	1	A
2	2	B
3	3	C
4	4	D
5	5	A,B
6	6	A,C
7	7	A,D

Result:

Model	Category	Data1	Data2
Model1	A	1	1
Model1	A	5	5
Model1	A	6	6
Model1	A	7	7
Model2	B	2	2
Model2	B	5	5
Model3	C	3	3
Model3	C	6	6

[Anonymous]

Hi @PHO 

Here is another approach for your reference. 

Step 1: In Table2, split Category column by delimiter comma and expand the results into rows. 

This will transform Table2 into below format

Step 2: In Table1, use Merge queries feature to merge data from Table2 to Table1. Select Category column as matching column and select Left Outer join. After merging, expand the colums you want. This will finally give you the expected result. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[PHO]

Thanks this is also a great solution which I wasn't aware of.

[dufoq3 · Accepted solution]

@PHO

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PSc0xVNJRclSK1YFyjYBcJwTXGMh1VoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Model = _t, Category = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYq5DQAwCMR2oabJX5NkC5T918hxFJZsye5SRInJU5cKCzarwYLD6rDgsgYsMM13wic77wVfbPzvAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, Category = _t]),
    SplitColumnByDelimiter = Table.SplitColumn(Table2, "Category", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Category.1", "Category.2"}),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(SplitColumnByDelimiter, List.Select(Table.ColumnNames(SplitColumnByDelimiter), (x)=> not Text.StartsWith(x, "Category.")) , "Attribute", "Category"),
    MergedQueries = Table.NestedJoin(Table1, {"Category"}, UnpivotedOtherColumns, {"Category"}, "UnpivotedOtherColumns", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergedQueries, "UnpivotedOtherColumns", {"Data 1", "Data 2"}, {"Data 1", "Data 2"}),
    SortedRows = Table.Sort(Expanded,{{"Model", Order.Ascending}})
in
    SortedRows

[dufoq3]

Hi @PHO, yes it is possible. If you can provide sample data and expected result based on sample data - we can help you 😉