I have seen some questions similar to this and I have tried some but they don't return what I want.
I have one table that has a column with a program name. I have a second table that has a text field that contains one or more program names (separated by commas). I want for each record in table #1 to add a new column that if I can find the same program name in table #2 and to make this new column contain the value of a totally different column in table #2.
These tables both have a lot of other columns in them but I am only showing the ones I want for this task. The tables are not linked/associated to each other.
Table #1
Program Name
Table #2
Assigned programs - one column with 1 or more program names in it each separated by a comma
LOB
The new column should be the first found result.
Example data/result:
Table #1
| Program Name | Found LOB (new column) |
| Google | LOB1 |
| Netflix | LOB2 |
| Facebook | LOB1 |
| Twitter | None |
Table #2 (the same LOB could be assigned to more than one combination)
| Assigned Programs | LOB |
| Google, Facebook | LOB1 |
| Facebook | LOB3 |
| Netflix, Google | LOB2 |
| Netflix, Facebook | LOB2 |
I am not sure if this should be done in DAX or the Power Query (transformation) - not sure if it matters or which is better. I have tried some examples I have seen for both ways but those examples didn't fully work to bring back a value from a totally different column (some returned the searched word, or starting position, etc.). I tried the fuzzy search with merged queries but that didn't bring back just the LOB column value but inserted a table with a new column in it which isn't what I would like. I need this new column to be available for vizualizations with other columns from Table #1 (not shown).
