Forum Discussion

Iamnvt's avatar
Iamnvt
Continued Contributor
7 years ago
Solved

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

Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    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

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    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's avatar
      Iamnvt
      Continued Contributor
      7 years ago

      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
      • Nolock's avatar
        Nolock
        Resident Rockstar
        7 years ago

        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.