Data Flow - Parsing Rows with Expression Builder

Question

Working through parsing data in Synapse using data flows. Here is my data flow thus far :

Here is a sample of the data prior to using a derived column to parse through each of the records:

Here is what my data looks like once I use these expressions below to parse through the data. The expressions work fine except on a few case scenarios (highlighted in red). I've attempted using iif statements as well usecase(), but am not getting exactly what I'm wanting to see.
Expressions :
Column Expression
Location substring(Resource, 0, instr(Resource, '') - 1)
Asset substring(Resource, instr(Resource, '')+1, instr(Resource, '.') - (instr(Resource, '_')+1))
Attribute substring(Resource, instr(Resource, '.')+1, instr(Resource, '-VAL') - (instr(Resource, '.')+1))

Here is what I would like to create using the expressions :

I'm thinking to catch these different records the location expression should include something along the lines of : if ResourceName shows a "." before a "" , then split at ".", else split at "". If you have any direction, I would really appreciate the help. Thank you.

Answer 1

Do this all in 1 single Derived Column:

Step 1: Create a new local var like this: regexSplit(Resource,'\s|_|\.|-')
Step 2: Location = :local1[1]
Step 3: Asset = iif(size(:local1)<3,toString(null()),:local1[2])
Step 4: Attribute = iif(size(:local1)<3,:local1[2],:local1[3])