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.
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!
Solved! Go to 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.
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.
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...
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.
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.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |