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
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
| Model | Category |
| Model1 | A |
| Model2 | B |
| Model3 | C |
table2
| Data 1 | Data 2 | Category |
| 1 | 1 | A |
| 2 | 2 | B |
| 3 | 3 | C |
| 4 | 4 | D |
| 5 | 5 | A,B |
| 6 | 6 | A,C |
| 7 | 7 | A,D |
Result:
| Model | Category | Data1 | Data2 |
| Model1 | A | 1 | 1 |
| Model1 | A | 5 | 5 |
| Model1 | A | 6 | 6 |
| Model1 | A | 7 | 7 |
| Model2 | B | 2 | 2 |
| Model2 | B | 5 | 5 |
| Model3 | C | 3 | 3 |
| Model3 | C | 6 | 6 |
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