Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi - I am looking to do the following (example):
Table 1
ID | Flavor |
1 | Vanilla |
2 | Chocolate |
3 | Rocky Road |
4 | Cookies and Cream |
Table 2
Feedback ID | Feedback Text |
1001 | Vanilla is the best |
1002 | I like Cholocate ice cream |
1003 | my favorite is Cookies and Cream |
1004 | We enjoy eating vanilla ice cream during the summer |
1005 | I like ice cream |
Table 2 would search Table 1 based upon the Feedback Text (in Table 2) to see if it finds the word in Table 1 (Flavor).
The end result would be something like this:
Table 2 (new column - flavor)
Feedback ID | Feedback Text | Flavor (NEW COLUMN) |
1001 | Vanilla is the best | Vanilla |
1002 | I like Cholocate ice cream | Chocolate |
1003 | my favorite is Cookies and Cream | Cookies and Cream |
1004 | We enjoy eating vanilla ice cream during the summer | Vanilla |
1005 | I like ice cream | Unknown |
I am looking to do something in Power Query as the data loads from the two tables.
After the dataloads, table 1 is no long needed.
Any thoughts ?
Thanks - Jerry
Solved! Go to Solution.
@jerryr Cholocate <> Chocolate. Other than that
Table.AddColumn(
Table2,
"Flavor",
(w) =>
try Table1[Flavor]
{List.PositionOf(
Table1[Flavor],
w[Feedback Text],
Occurrence.First,
(x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)
)}
otherwise "Unknown"
)
Hi,
Thanks for the solution @AlienSx provided, and i want to offer some more information for user to refer to.
hello @jerryr , you can add a custom column.
let a=List.Transform(#"Table 1"[Flavor],each Text.Lower(_)),
b=Text.Lower([Feedback Text])
in try Text.Proper( List.Select(a,each Text.Contains(b,_)){0}) otherwise "Unknown"
Output
And you can refer to the attachment.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for the solution @AlienSx provided, and i want to offer some more information for user to refer to.
hello @jerryr , you can add a custom column.
let a=List.Transform(#"Table 1"[Flavor],each Text.Lower(_)),
b=Text.Lower([Feedback Text])
in try Text.Proper( List.Select(a,each Text.Contains(b,_)){0}) otherwise "Unknown"
Output
And you can refer to the attachment.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@jerryr Cholocate <> Chocolate. Other than that
Table.AddColumn(
Table2,
"Flavor",
(w) =>
try Table1[Flavor]
{List.PositionOf(
Table1[Flavor],
w[Feedback Text],
Occurrence.First,
(x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)
)}
otherwise "Unknown"
)
Check out the July 2025 Power BI update to learn about new features.