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.
I want to fetch the campaign string from the column Tags in my table if the value in the title column also contains campaign word and put it into another column. I want to search for the word "Campaign" in the tags column (so that in future if new campaign tags are added I won't have to update my code) and if it is present, I want to fetch just the campaign string present and no other tags. I can't use the split column using delimiter because not every row has this tag.
eg:
Title Tags CampaignTagsWanted
Campaign Product A Campaign Jan, XYZ Tag Campaign Jan
Campaign Product B Campaign Feb, ABC Tag Campaign Feb
Campaign Product C Mar, LMN Tag
Product D ABCD Tag, LMN Tag
Solved! Go to Solution.
Could you accept to use Edit queries in Power BI?
I make a test as below
Please download my pbix file, open Edit queries,
click on the setp and the icon on right to see details for each step.
Steps:
In Edit queries,
1. Add column->add index column from 1
2. copy Table 1, paste to get Table 2
3. in Table2, click on column "Tags ", Transform->Split column->By delimiter "comma"
4. select "Tags .1" and "Tags .2", Transform->"Unpovit columns"
5. click on "Value" column, Transform->Format->trim
6. split column "Value" by delimeter 'space"
7. Add column->conditional column
8. click on "Value.1" and "Value.2" columns, Add column->merge columns, thus get a column "Merged"
9. filter rows: remove empty for "condition1",
10. select "Title", "Index","Merged", then Home->remove other columns
11. go to Table1, merge queries (Table1 and Table2) based on "index" column
Expand column "merged" from Table2
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Could you accept to use Edit queries in Power BI?
I make a test as below
Please download my pbix file, open Edit queries,
click on the setp and the icon on right to see details for each step.
Steps:
In Edit queries,
1. Add column->add index column from 1
2. copy Table 1, paste to get Table 2
3. in Table2, click on column "Tags ", Transform->Split column->By delimiter "comma"
4. select "Tags .1" and "Tags .2", Transform->"Unpovit columns"
5. click on "Value" column, Transform->Format->trim
6. split column "Value" by delimeter 'space"
7. Add column->conditional column
8. click on "Value.1" and "Value.2" columns, Add column->merge columns, thus get a column "Merged"
9. filter rows: remove empty for "condition1",
10. select "Title", "Index","Merged", then Home->remove other columns
11. go to Table1, merge queries (Table1 and Table2) based on "index" column
Expand column "merged" from Table2
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Does this give you the result you are after?
CampaignTagsWanted = IF((SEARCH("Campaign", [Tags], 1, 0) > 0), LEFT([Tags],(FIND(",",[Tags],1,1)-1)))
No, It is giving me blank rows.
Is it only returning blank rows?
And is it possible for you to post some more sample data
I have attached a photo in the main query for reference.
Right,
One question
In the tags column are the campaign values always the first value in the string?
No. The tags can be anywhere in the list.
Alright, that made it a bit worse
How about the delimiters, is there a max amount of delimiters or can there be an endless amount?
There can be multiple tags. There is no specific count for the number of delimiters.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |