Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
juuju
New Member

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 columnDesired columnDesired column 2
Example:AA:XX-yy:1234XX-yy1234

 

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!

 

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

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

2017-10-23_12-33-47.png



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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:

PBI_Extract_String.jpg 

It results in this formula:

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Super User
Super User

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-...

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

TomMartens
Super User
Super User

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

2017-10-23_12-33-47.png



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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:

PBI_Extract_String.jpg 

It results in this formula:

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.