Forum Discussion

PHO's avatar
PHO
Frequent Visitor
2 years ago
Solved

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

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  sepereat...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
PHO's avatar
PHO
Frequent Visitor
2 years ago

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

AlienSx's avatar
AlienSx
Super User
2 years ago

 

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's avatar
    PHO
    Frequent Visitor
    2 years ago

    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

    ModelCategory
    Model1A
    Model2B
    Model3C


    table2

    Data 1Data 2Category
    11A
    22B
    33C
    44D
    55A,B
    66A,C
    77A,D

    Result:

    ModelCategoryData1Data2
    Model1A11
    Model1A55
    Model1A66
    Model1A77
    Model2B22
    Model2B55
    Model3C33
    Model3C66
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        PHO
        Frequent Visitor
        2 years ago

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

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      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