Forum Discussion
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 sepereated value in Table 2 which I had splitted.
________________________________________________________
So I want to find out whether a product group from Table 1 is contained in Table 2, in which more than one product group is grouped. Perhaps a "contains merge" would also work for the grouped non splitted Column.
I wasn't able to do this with fuzzy matching every time something was missing.
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
8 Replies
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/l6LhnmE4Nxqrlet 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 filterI 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 1Model Category Model1 A Model2 B Model3 C
table2Data 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
