Forum Discussion
DAX: extracting string using delimiter
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!
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
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})
4 Replies
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
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})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
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-p/963216
