Get last value from an delimited set of values

[graemejohnson]

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?

[Anonymous]

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. 

[graemejohnson · Accepted 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

[Anonymous]

Use Power Query for this. Not DAX. DAX is a data analysis language, not data manipulation language. M is.

Best
Darek