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
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