Creating a column based on a piece of text in another column

[bosleyjarrett]

Hello,

We have a naming convention for our internal systems that utilizes a | to delineate a number of variables in the name itself. For example, Fish in Florida would be d:Fish|g:Florida|. Is there a way to write a formula in dax (or power query) that would pull just 'Fish' from the example? there is a lot of variation in lengths between different names (ex. d:Goldfish|g:Texas|) so I need some sort of wild card to pull just the text between the d: and the next | . every name has a d: with a | separating it from the next piece of the naming convention. How would I write this?

[erik_tarnvik]

I would definitly recommend using Power Query as advised by @vega. The question I have is, is the format uniform? Meaning, is a d:Text deliminated by | always followed by a g:Text|? Or could the order be reversed where g:Text| precedes d:text? If that is the case it becomes more involved but can still be done. 

[vega · Accepted solution]

If you want a calculated column you can use the following DAX code. Just change the name "Test" to the name of the column that contains your data:

answer = 
PATHITEM(SUBSTITUTE(Table1[Test], ":", "|"), 2)

You can see the result here:

If you want to do it with PowerQuery, you can use the "Split column by delimiter" button and split by the delimiter of your choice until you isolate the value you want. So, in this case, you would split by ":", followed by a split by "|". If you need to keep the original value of the column you can create a copy of the column and then do the split column by delimiter.

Below you can see the option for "Split column by delimiter"

[bosleyjarrett]

Thanks for the response!

That DAX syntax is not working in my actual dataset, which I'm pretty sure is due to the fact that the piece of the naming convention I am looking to pull is not first in the order (ex. b:___|c:___|d:what I want|). The number of characters preceding the d: is not uniform either, adding an additional layer of complexity.

I'd really like to be able to do this in the DAX as there are over 10 pieces in each name that I'd rather not have to create columns for in my tables but if thats the only way to do it, then its not a huge deal.

[erik_tarnvik]

If you want to do this in DAX your can use the below as a template. I assume you have a table called Fish with a column Str that follows your pattern. The below would pull the string bwteen "d:" and "|" regardless of where it occurs in your string. Edit the "d:" to get other elements. If There are no matching elements in Fish[Str] a BLANK() is returned. Let me know if this doesn't help.

Column = 
VAR D1 = FIND("d:",Fish[Str],1,0)
VAR D2 = IF(D1 > 0, FIND("|", Fish[Str], D1, 0), 0)
RETURN
   IF(D1 > 0 && D2 >0, MID(Fish[Str],D1+2,D2 - D1 - 2), BLANK())

[vega]

So, in the DAX code, SUBSTITUTE is changing the ":" into "|". Once that is done, you have a text containing words separated by "|". Then, PATHITEM's second parameter is the word number that you what to extract from the text ( the first word in the text is 1, the second word is 2, etc.). So, with your example of "b:___|c:___|d:what I want|", you'd want to use 6 as the parameter for PATHITEM to get to "what I want".  

[bosleyjarrett]

That makes perfect sense. Thanks!