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.
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,
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |