Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi,
I have 2 tables:
Column1
A, B |
B, D |
E, F |
and
Column1Column2
A | 12 |
E | 22 |
D | 10 |
how can I merge the 2 tables together, as long as the text from table 1 contains any value from Text of column 1 in Table 2.
Expected Result:
Column1 | Value |
A, B | 12 |
B, D | 10 |
E, F | 22 |
@Nolock : pls take a look at this as well. Thank you
Solved! Go to Solution.
Hi @Iamnvt,
here we go:
Table TableWordValue:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UorViVZyBTKNIEwXkKiBUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Word = _t, Value = _t]) in Source
And the logic is in the other table TablePairs:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcFKK1YlWctJRcAEzXHUU3JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), // duplicate the column Column1 DuplicateColumn = Table.DuplicateColumn(Source, "Column1", "ToSplit"), // split the column by ", " Split = Table.SplitColumn(DuplicateColumn, "ToSplit", Splitter.SplitTextByDelimiter(", "), {"First", "Second"}), // join on first column with TableWordValue table FirstJoin = Table.Join(Split, "First", TableWordValue, "Word", JoinKind.LeftOuter), RemoveWordColumn = Table.RemoveColumns(FirstJoin, {"Word"}), RenameValueColumn = Table.RenameColumns(RemoveWordColumn, {{"Value", "First.Value"}}), // join on second column with TableWordValue table SecondJoin = Table.Join(RenameValueColumn, "Second", TableWordValue, "Word", JoinKind.LeftOuter), RemoveWordColumn2 = Table.RemoveColumns(SecondJoin, {"Word"}), RenameValueColumn2 = Table.RenameColumns(RemoveWordColumn2, {{"Value", "Second.Value"}}), // create result column as combination of First and Second values ValueOfValueColumn = Table.AddColumn(RenameValueColumn2, "Result", each if [First.Value] = null then [Second.Value] else [First.Value]), // remove temp columns RemoveValues = Table.RemoveColumns(ValueOfValueColumn, {"First.Value", "Second.Value", "First", "Second"}) in RemoveValues
Hi @Iamnvt,
here we go:
Table TableWordValue:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UorViVZyBTKNIEwXkKiBUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Word = _t, Value = _t]) in Source
And the logic is in the other table TablePairs:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcFKK1YlWctJRcAEzXHUU3JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), // duplicate the column Column1 DuplicateColumn = Table.DuplicateColumn(Source, "Column1", "ToSplit"), // split the column by ", " Split = Table.SplitColumn(DuplicateColumn, "ToSplit", Splitter.SplitTextByDelimiter(", "), {"First", "Second"}), // join on first column with TableWordValue table FirstJoin = Table.Join(Split, "First", TableWordValue, "Word", JoinKind.LeftOuter), RemoveWordColumn = Table.RemoveColumns(FirstJoin, {"Word"}), RenameValueColumn = Table.RenameColumns(RemoveWordColumn, {{"Value", "First.Value"}}), // join on second column with TableWordValue table SecondJoin = Table.Join(RenameValueColumn, "Second", TableWordValue, "Word", JoinKind.LeftOuter), RemoveWordColumn2 = Table.RemoveColumns(SecondJoin, {"Word"}), RenameValueColumn2 = Table.RenameColumns(RemoveWordColumn2, {{"Value", "Second.Value"}}), // create result column as combination of First and Second values ValueOfValueColumn = Table.AddColumn(RenameValueColumn2, "Result", each if [First.Value] = null then [Second.Value] else [First.Value]), // remove temp columns RemoveValues = Table.RemoveColumns(ValueOfValueColumn, {"First.Value", "Second.Value", "First", "Second"}) in RemoveValues
@Nolock Thank you very much for the solution. However, I am looking for more generalized approach. Here is a bit more complex scenarios:
Table 1 have multiple values, separated by Comma
Column1 |
A, B, C |
B, D |
E, F |
Table 2: have many result values:
Word | Value |
A | 12 |
E | 22 |
D | 10 |
B | 5 |
Expected Merged result:
Column1 | Merged with | Result |
A, B, C | A | 12 |
A, B, C | B | 5 |
B, D | B | 5 |
B, D | D | 10 |
E, F | E | 22 |
Hi @Iamnvt,
the requirements have changed dramatically and there is a solution but with a different approach.
Therefore I would suggest to close this topic, mark it as answered (if you don't mind), and open another topic. If you mention me in the new post, I'll solve it asap.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.