topic Re: Get last value from an delimited set of values in DAX Commands and Tips

Get last value from an delimited set of values

graemejohnson — Thu, 01 Aug 2019 16:09:37 GMT

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 :smileyfrustrated: - please can someone help?

Re: Get last value from an delimited set of values

Anonymous — Thu, 01 Aug 2019 17:11:58 GMT

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

Best
Darek

Re: Get last value from an delimited set of values

Anonymous — Thu, 01 Aug 2019 17:12:48 GMT

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.

Re: Get last value from an delimited set of values

graemejohnson — Fri, 02 Aug 2019 08:28:17 GMT

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