DAX: extracting string using delimiter

[juuju]

Hi guys, newbie to the forum! I've recently discovered PowerPivot and it's fantastic - but do occassionaly encounter issues when converting Excel formulas to DAX-friendly formulas.

I have a column in my data model table which has values like this: Example:AA:XX-yy:1234 --> I would like to create 2 additional columns which contains only XX-yy (text between 2nd/3rd colon delimiter) and 1234 (text after the last colon delimiter)

Origin column	Desired column	Desired column 2
Example:AA:XX-yy:1234	XX-yy	1234

As the data refreshes from a SQL server, I want to have dynamic columns instead of using the text-to-columns function manually.

The excel formula I used before looks like this:

xx = origin cell | 2 takes the value between the 2nd/3rd colon delimter

=TRIM(LEFT(SUBSTITUTE(MID(xx,FIND("|",SUBSTITUTE(xx,":","|",2))+1,LEN(xx)),":",REPT(" ",LEN(xx))),LEN(xx)))

Any help would be greatly appreciated! Thank you!

[TomMartens · Accepted solution]

Hey,

besides the fact that I would try to create the columns using the Query Editor here are two DAX Statements to create Calculated Columns

First Derived Column = 
var FirstColon = FIND(":",'Table1'[Origin Column],1)
var SecondColon = FIND(":",'Table1'[Origin Column],FirstColon + 1)
var ThirdColon = FIND(":",'Table1'[Origin Column],SecondColon + 1)
return
MID('Table1'[Origin Column], SecondColon + 1, ThirdColon - SecondColon - 1)

and

Second Derived Column = 
var FirstColon = FIND(":",'Table1'[Origin Column],1)
var SecondColon = FIND(":",'Table1'[Origin Column],FirstColon + 1)
var ThirdColon = FIND(":",'Table1'[Origin Column],SecondColon + 1)
var LengthOfString = LEN('Table1'[Origin Column])
return
MID('Table1'[Origin Column],ThirdColon + 1, LengthOfString -  ThirdColon + 1)

this would create the following

[ImkeF]

Hi @Manichin ,
you can use an additional formula on the returned result: List.Select

List.Select(<YourResultSoFar>, each Text.StartsWith(_, "KeyThatYouWant"))

This will pick that item from your value pairs, that starts with the desired text.
Or are these elements proper records already?
Otherwise please provide sample data like described here:

Upload sample data / tables: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[TomMartens · Accepted solution]

Hey,

besides the fact that I would try to create the columns using the Query Editor here are two DAX Statements to create Calculated Columns

First Derived Column = 
var FirstColon = FIND(":",'Table1'[Origin Column],1)
var SecondColon = FIND(":",'Table1'[Origin Column],FirstColon + 1)
var ThirdColon = FIND(":",'Table1'[Origin Column],SecondColon + 1)
return
MID('Table1'[Origin Column], SecondColon + 1, ThirdColon - SecondColon - 1)

and

Second Derived Column = 
var FirstColon = FIND(":",'Table1'[Origin Column],1)
var SecondColon = FIND(":",'Table1'[Origin Column],FirstColon + 1)
var ThirdColon = FIND(":",'Table1'[Origin Column],SecondColon + 1)
var LengthOfString = LEN('Table1'[Origin Column])
return
MID('Table1'[Origin Column],ThirdColon + 1, LengthOfString -  ThirdColon + 1)

this would create the following

[ImkeF · Accepted solution]

As @TomMartens mentioned already, this task can easier be done in Power Query /the query editor in PBI.

If you want to extract the values between the 2nd and 3rd colon delimiter, you add a column with this formula:

 Text.Split([Value], ":"){2}

It splits the text on each colon and returns a list of the separated values. To fetch the 3rd value from the list you use {2} because the count starts at zero here.

To extract the values after the last colon, there is a nice user interface for it:

 

It results in this formula:

Text.AfterDelimiter([Value], ":", {0, RelativePosition.FromEnd})

[Manichin]

Hi ImkeF, Thank you for your input. I have the same situation where i need to get a key:value pair which is not in the same no of key value pair ineachcolumn. 2 nd row will have 3 key:value pairs and 3 rd might have 6 key:value pairs. i need to get a specific key:value pair from that column. could you suggest how to get it. Please let me know if you need more inputs