cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Iamnvt Member
Member

Merge by Text Contain Any

hi,

 

I have 2 tables:

Column1

A, B
B, D
E, F

 

and 

 

Column1Column2

A12
E22
D10

 

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:

Column1Value
A, B12
B, D10
E, F22

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Merge by Text Contain Any

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
3 REPLIES 3
Super User
Super User

Re: Merge by Text Contain Any

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 Member
Member

Re: Merge by Text Contain Any

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

 

WordValue
A12
E22
D10
B5

 

Expected Merged result:

 

Column1Merged withResult
A, B, CA12
A, B, CB5
B, DB5
B, DD10
E, FE22
Super User
Super User

Re: Merge by Text Contain Any

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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 120 members 1,755 guests
Please welcome our newest community members: