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 would like to know if there is a work around or if it is possible at all to use a variable in the SEARCH function.
I theoretically want to find the second occurance of a value in a string.
First, I set a variable to the position of the first occurance, if there is none I set it to 0.
Then, I want to return the next occurance by starting at the position that is set in the variable. But the Search function does not allow the variable.
If there is a different way to do this please share.
Regards
Hugh
Solved! Go to Solution.
Hi, @HughLa ;
Since the position of search() cannot start from 0, because the result after your first search has 0; So the second lookup needs to start at +1. You can refer to the following formula:
Column =
var _posi=SEARCH("-",[NAME],1,0)
return SEARCH("-",[NAME],_posi+1,_posi)
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @HughLa ;
Since the position of search() cannot start from 0, because the result after your first search has 0; So the second lookup needs to start at +1. You can refer to the following formula:
Column =
var _posi=SEARCH("-",[NAME],1,0)
return SEARCH("-",[NAME],_posi+1,_posi)
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@HughLa ,
You are on the right track with the Search. Here is a solution using multiple columns.
1. Length = LEN([ProductDescription]) // Find the length of your [ProductDescription]
2. Search 1st = SEARCH("-", [ProductDescription],,0 ) // Finds first occurrence of "-"
3. MID = MID( [Name], [Search 1st]+1, [Length] - [Search 1st]+1 ) // Extract the remainder of the String after the first occurrence
4. Search 2nd = SEARCH("-", [MID],,0 ) // Apply the Search function again on the Remaining string.
NAME | Length | Search 1st | MID | Search 2nd |
1110009988 | 10 | 0 | 1110009988 | 0 |
88888-GGGG-DDDD- | 16 | 6 | GGGG-DDDD- | 5 |
AGL-0001 | 8 | 4 | 0001 | 0 |
2344778678 | 10 | 0 | 2344778678 | 0 |
5278-837HJ- | 11 | 5 | 837HJ- | 6 |
DHL-88747474 | 12 | 4 | 88747474 | 0 |
BIG-XX87655 | 11 | 4 | XX87655 | 0 |
JIB-129499- | 11 | 4 | 129499- | 7 |
37HHU7733 | 9 | 0 | 37HHU7733 | 0 |
With additional effort, you can combine all of this in one column. I wanted to clearly demonstrate the logic behind my approach.
Hope you can put this to good use.
Regards,
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
62 | |
62 | |
52 | |
39 | |
24 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |