Substring with DAX and Power Query

[v-alq-msft]

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

=Text.End( 
    [WF], 
    Text.PositionOfAny( 
        Text.Reverse([WF]), 
        {"-","("} 
    )+1 
) 

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 =
    ADDCOLUMNS (
        GENERATESERIES ( 1, LEN ( [WF] ), 1 ),
        "Char", LEFT ( RIGHT ( [WF], [Value] ), 1 )
    )
VAR minIndex =
    MINX ( FILTER ( t, [Char] = UNICHAR ( 40 ) || [Char] = "-" ), [Value] )
RETURN
    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)"
RETURN
    ADDCOLUMNS (
        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

substring dax pq.pbix