Forum Discussion
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...
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
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!
Thanks this is also a great solution which I wasn't aware of.