Merge by Text Contain Any

[Iamnvt]

hi,

I have 2 tables:

Column1

A, B

B, D

E, F

and 

Column1Column2

A	12
E	22
D	10

how can I merge the 2 tables together, as long as the text from table 1 contains any value from Text of column 1 in Table 2.

Expected Result:

Column1	Value
A, B	12
B, D	10
E, F	22

@Nolock : pls take a look at this as well. Thank you

[Nolock · Accepted solution]

Hi @Iamnvt,

here we go:

Table TableWordValue:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UorViVZyBTKNIEwXkKiBUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Word = _t, Value = _t])
in
    Source

And the logic is in the other table TablePairs:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcFKK1YlWctJRcAEzXHUU3JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    // duplicate the column Column1
    DuplicateColumn = Table.DuplicateColumn(Source, "Column1", "ToSplit"),
    // split the column by ", "
    Split = Table.SplitColumn(DuplicateColumn, "ToSplit", Splitter.SplitTextByDelimiter(", "), {"First", "Second"}),
    // join on first column with TableWordValue table
    FirstJoin = Table.Join(Split, "First", TableWordValue, "Word", JoinKind.LeftOuter),
    RemoveWordColumn = Table.RemoveColumns(FirstJoin, {"Word"}),
    RenameValueColumn = Table.RenameColumns(RemoveWordColumn, {{"Value", "First.Value"}}),
    // join on second column with TableWordValue table
    SecondJoin = Table.Join(RenameValueColumn, "Second", TableWordValue, "Word", JoinKind.LeftOuter),
    RemoveWordColumn2 = Table.RemoveColumns(SecondJoin, {"Word"}),
    RenameValueColumn2 = Table.RenameColumns(RemoveWordColumn2, {{"Value", "Second.Value"}}),
    // create result column as combination of First and Second values
    ValueOfValueColumn = Table.AddColumn(RenameValueColumn2, "Result", each if [First.Value] = null then [Second.Value] else [First.Value]),
    // remove temp columns
    RemoveValues = Table.RemoveColumns(ValueOfValueColumn, {"First.Value", "Second.Value", "First", "Second"})  
in
    RemoveValues

[Iamnvt]

@Nolock  Thank you very much for the solution. However, I am looking for more generalized approach. Here is a bit more complex scenarios:

Table 1 have multiple values, separated by Comma

Column1

A, B, C

B, D

E, F

Table 2: have many result values:

Word	Value
A	12
E	22
D	10
B	5

Expected Merged result:

Column1	Merged with	Result
A, B, C	A	12
A, B, C	B	5
B, D	B	5
B, D	D	10
E, F	E	22

[Nolock]

Hi @Iamnvt,

the requirements have changed dramatically and there is a solution but with a different approach.

Therefore I would suggest to close this topic, mark it as answered (if you don't mind), and open another topic. If you mention me in the new post, I'll solve it asap.