Forum Discussion

OSSKD1's avatar
OSSKD1
New Member
3 years ago
Solved

Extract specific text from string

I need to extract the 6 digit number, dash, and either 1,2, or 3 digit number following the dash from the dataset shown below. What is the easiest way to accomplish this as there isn't consistency in...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi OSSKD1 
    Please refer to attched file with the solution

    Result = 
VAR String = 'Table'[Text]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" )
VAR T2 = GENERATESERIES ( 1, Length, 1 )
VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) )
VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) )
VAR Result = CONCATENATEX ( T4, [@Item], "-" )
RETURN
    IF ( CONTAINSSTRING ( Result, "-" ), Result )
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi OSSKD1 
    Attached file with small correction

    Result = 
VAR String = 'Table'[Text]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" )
VAR T2 = GENERATESERIES ( 1, Length, 1 )
VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) )
VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) )
VAR Result = CONCATENATEX ( T4, [@Item], "-", [Value], ASC )
RETURN
    IF ( CONTAINSSTRING ( Result, "-" ), Result )
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi OSSKD1 
Please refer to attched file with the solution

Result = 
VAR String = 'Table'[Text]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" )
VAR T2 = GENERATESERIES ( 1, Length, 1 )
VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) )
VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) )
VAR Result = CONCATENATEX ( T4, [@Item], "-" )
RETURN
    IF ( CONTAINSSTRING ( Result, "-" ), Result )
OSSKD1's avatar
OSSKD1
New Member
3 years ago

Hi tamerj1,

 

Quick follow up from yesterday's solution. I noticed in my master data and also in the file you provided, that in certain rows, the sequence was reversed (the 3 digits are now in front and the 6 digits are last) per the screenshot below. Any ideas what in the DAX might cause this?

 

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi OSSKD1 
    Attached file with small correction

    Result = 
VAR String = 'Table'[Text]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" )
VAR T2 = GENERATESERIES ( 1, Length, 1 )
VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) )
VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) )
VAR Result = CONCATENATEX ( T4, [@Item], "-", [Value], ASC )
RETURN
    IF ( CONTAINSSTRING ( Result, "-" ), Result )
    • OSSKD1's avatar
      OSSKD1
      New Member
      3 years ago

      Thank you! That solved all my issues, very much appreciated!