Forum Discussion

DimaMD's avatar
DimaMD
Icon for Solution Sage rankSolution Sage
4 years ago
Solved

Search for text among numeric values

Hello community.   I need to solve one task. In collum have data that starts with numbers, then we have text, then again numbers, the goal is to select the text between two numbers and to insert it...
@dax @help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi DimaMD 
    As promissed, here is the solution (No need for the letters table)

    Text = 
VAR String = 'String Table'[String]
VAR StringLength = LEN ( String )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Number", [Value] & "" )
VAR T2 = SELECTCOLUMNS ( GENERATESERIES ( 1, StringLength ), "@Index", [Value] )
VAR T3 = ADDCOLUMNS ( T2, "@StringLetters", MID ( String, [@Index], 1 ) )
VAR T4 = ADDCOLUMNS ( T3, "@TexLetters", IF ( NOT ( [@StringLetters] IN T1 ), LOWER ( [@StringLetters] ), "|" ) )
VAR T5 = 
    ADDCOLUMNS ( 
        T4, 
        "Text Letters", 
        VAR PreviousIndex = [@Index] - 1
        RETURN
            IF ( MAXX ( FILTER ( T4, [@Index] = PreviousIndex ), [@TexLetters] )  <> "|", [@TexLetters] ) )
VAR T6 = FILTER ( T5, [Text Letters] <> BLANK ( ) )
RETURN
    PATHITEM ( CONCATENATEX ( T6, [Text Letters] ), 2 )

     

DimaMD's avatar
DimaMD
Icon for Solution Sage rankSolution Sage
4 years ago

Hi tamerj1 in collum1 we have original data. The goal is to make "result" collum, which will copy text, that is located betweeen numbers in collum 1. 

Collum1result
000235123 Example text between numbers 234234424Example text between numbers
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi DimaMD 
First step is to create a seperate table containing all the letters that you consider as string

Text Letters = 
SELECTCOLUMNS ( 
    { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", " ", "-", "_", ")", "(", "[", "]", ",", ";", ":", "{", "}", "*", "&", "%", "$", "#", "@", "!", "?", "<", ">", "+", "=", "." }, 
    "Letter", [Value] 
)

Then create new column

Text = 
VAR ValueLength = LEN ( 'Table'[String] )
VAR T3 = GENERATESERIES ( 1, ValueLength )
VAR T4 = ADDCOLUMNS ( T3, "@StringLetter", MID ( 'Table'[String], [Value], 1 ) )
VAR T5 = ADDCOLUMNS ( T4, "@TexLetters", IF ( [@StringLetter] IN VALUES ( 'Text Letters'[Letter] ), LOWER ( [@StringLetter] ) ) )
RETURN
    CONCATENATEX ( T5, [@TexLetters] )