If Table 1 column A contains string from Table 2 column A then return string in Column B

[Anonymous]

Hello, 

I'm new to PBI and I have what I thought would be a common need, but I can't find a solution. Probably due to me not searching with the right terminology. 

The situation is I have google analytics data in Table 1, and in column A are page urls. In Table 2 I have in Column A what I want to look for within a URL, and in the other column I have the text string I'd like returned in a new column in Table 1 if a match is found

Table 1 (before what i'm looking for happens)

Page URL

/mountain-climbing

/climbing/blog

/swimming-ocean

/swimming

Table 2

Keyword	Label
climbing	Mountain Climbers
swim	Swimmers

Goal: Table 1 after new column generated

Page URL	Label
/mountain-climbing	Mountain Climbers
/climbing/blog	Mountain Climbers
/swimming-ocean	Swimmers
/swimming	Swimmers

Any guidance appreciated!

[Cmcmahan]

Honestly, Power BI is not a great tool for performing this kind of transformation. There are ways to do it, utilizing the SEARCH command within DAX or other table transformations within Power Query, but none of them are anywhere close to easy or intuitive. Here's a post trying to do something similar. Try and follow that example, and let us know if you need additional help.

In addition, what do you want to happen if multiple keywords appear within the same URL? If the keyword "mountain" is added with the Label "Mountaineering" what would "/mountain-climbing" look like in your final table?

The common solutions are to add the URL to multiple rows, concatenate the labels in one column, or create a new column for each label. Each has their own strengths and weaknesses.

[Anonymous]

Thank you for your response. It is affirming that I wasn't just missing something easy. I'll dig into the approach you linked and let you know how it turns out! 

"In addition, what do you want to happen if multiple keywords appear within the same URL? If the keyword mountain" is added with the Label "Mountaineering" what would "/mountain-climbing" look like in your final table?"

I had thought about this a little and for now had settled on just treating it like a conditional column and organizing them in a way that the first match is what is used. It is problematic though...

[Cmcmahan · Accepted solution]

The approach I would suggest is having your URLs in one table like they are now, and keywords/labels in another.  Then I would create a completely new table that uses URL and labels as foreign keys.  Then it makes sense to have a row for each URL/label combination.

As far as how to accomplish this, I would consider seeing if you can figure out a way to get Google analytics or another program to do it instead of Power BI. Power Query and DAX have very strong data modeling capabilities, but searching within every string in a column doesn't appear to be one of them.