Substring with DAX and Power Query

Suppose I would like to substring text from first specific character or symbol. How will I achieve this requirement? 


Table Used: 




The requirement is to find the first ‘(’ or ‘-’ from right to left and extract the text from the position of the specific character to the end. 


Here are two methods to achieve the same: 

  • Power Query Method 
  • Dax Method 


Power Query Method: 

In Power Query, we can create a custom column with the following m codes. 







Let me explain the process.  
1. Since we need to get the position of the specified character from right to left in the original text, we can use ‘Text.Reverse()’ to reverse the text and then use ‘Text.PositionOfAny()’ to get the position of first specific character.  
2. Because the index starts at 0 in ‘Text.PositionOfAny’ and ‘Text.End()’ extracts the specific number of characters, we need to add ‘+1’ to the position.  

DAX Method: 

In DAX, we may create a calculated column as below. 


Dax Document type =
VAR t =
        GENERATESERIES ( 1, LEN ( [WF] ), 1 ),
        "Char", LEFT ( RIGHT ( [WF], [Value] ), 1 )
VAR minIndex =
    MINX ( FILTER ( t, [Char] = UNICHAR ( 40 ) || [Char] = "-" ), [Value] )
    RIGHT ( [WF], minIndex )



  1. We use variable to keep a table which has a column with index from 1 to its length and a column called ‘Char’ keeping the position of specific character from right to left.  
  2. ‘Right’ is to return the last character or characters in a text string based on the value of ‘Value’ column.  
  3. Then we may use ‘Left’ to extract the first character of the text.  
  4. The result table has a list of characters and a column indicating the position of the character from right to left. You may test by creating a calculated table as below. 


Test =
VAR _text = "Subcontractor Submission to Something (Procedure)"
        GENERATESERIES ( 1, LEN ( _text ), 1 ),
        "Char", LEFT ( RIGHT ( _text, [Value] ), 1 )








5. Since we have got the position, the rest steps are easy to do with DAX. We can use ‘MINX’ to get the first position of ‘(’ or ‘-’ and finally extract the text from right to left based on the position. 


This is how one can use Power Query or DAX to substring text from first specific character or symbol. Hope this article helps everyone with similar questions here. 


Author: Allan Qin

Reviewer: Ula Huang, Kerry Wang


Source link

Be the first to comment

Leave a Reply

Your email address will not be published.