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 find the starting position of one text string within another text string, reading right to left.
Below is the value from which I want retrieve the color name (Silver).
Contoso Optical Wheel OEM PS/2 Mouse E60 Silver
I tried using Search function but unfortunatly it did'nt work for me because it starts reading the text from leff to right.
RIGHT([Product Name],SEARCH(" ",[Product Name],1,BLANK()))
Please suggest a function or approch using which I can get the desired result.
Solved! Go to Solution.
Sorry, I misread the question! It's a bit more tricky. I got the below to work. It depends on identifying the position of the last space in the string before using the first method again
Column =
VAR __NumberOfSpaces =
LEN('Table'[Product Name])-len(SUBSTITUTE('Table'[Product Name]," ","")) //Find how many spaces are in the string
return
RIGHT( //The same basic structure: RIGHT(), LEN() ans SEARCH()
[Product Name],
LEN([Product Name]) -
SEARCH("@", //Search for "@" (any character that you
//choose that do not exist in the strings beforehand)
SUBSTITUTE('Table'[Product Name]," ","@",__NumberOfSpaces)
//Use SUBSTITUTE() to replace space with "@" only on
//the last (__NumberOfSpaces) occurence of space.
,1,
BLANK()
)
)
Hi!
You can combine LEN() and SEARCH() to mimic right-to-left reading.
I haven't double-checked this code, but something like below should work:
RIGHT([Product Name],LEN([Product Name])-(SEARCH(" ",[Product Name],1,BLANK()))
Let me know if it doesn't and I'll take a closer look!
Sorry, I misread the question! It's a bit more tricky. I got the below to work. It depends on identifying the position of the last space in the string before using the first method again
Column =
VAR __NumberOfSpaces =
LEN('Table'[Product Name])-len(SUBSTITUTE('Table'[Product Name]," ","")) //Find how many spaces are in the string
return
RIGHT( //The same basic structure: RIGHT(), LEN() ans SEARCH()
[Product Name],
LEN([Product Name]) -
SEARCH("@", //Search for "@" (any character that you
//choose that do not exist in the strings beforehand)
SUBSTITUTE('Table'[Product Name]," ","@",__NumberOfSpaces)
//Use SUBSTITUTE() to replace space with "@" only on
//the last (__NumberOfSpaces) occurence of space.
,1,
BLANK()
)
)
There also seems to be a working method using PowerQuery:
Solved: Search from right to left - Microsoft Power BI Community
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |