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 have a column in a table which contains zero, one or more delimited values. I want to extract value on the right hand side of the last delimiter if there is a delimer...for example the values highlighted in yellow below....
This is tying me up in knots trying to crack it - please can someone help?
Solved! Go to Solution.
Well, having had a further sniff around....I saw the 'Extract' option - and used that functionality to grab the last value
Also then had to change the data type to a Whole Number
Seems a neat solution
This type of thing is much easier in Power Query in my view. Assuming that's an option:
1. Add a custom column that will give you the position of the last delimiter
Text.PositionOfAny([Column1],{","}, Occurrence.Last)
2. Custom column to get the text lenght of Column 1
Text.Length([Column1])
3. Final custom column to get the value of the last delimited value
if [Position of Last Delimiter] < 0 then [Column1] else Text.End([Column1],([Text Length]-[Position of Last Delimiter]-1) )
Basically it's saying that is the position of last delimiter is negative (which means there is no delimiter) then give the value in that row. Else, start at the end of values in the current row, and go back to you find the first delimiter (which would be the last delimiter)
Final Table:
You could easily combine #1 and #2 into one step, but left them on here to show the idea.
Well, having had a further sniff around....I saw the 'Extract' option - and used that functionality to grab the last value
Also then had to change the data type to a Whole Number
Seems a neat solution
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
42 | |
41 | |
37 | |
19 | |
19 |