Forum Discussion

DimaMD's avatar
DimaMD
Solution 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 )

     

Amy_Qc's avatar
Amy_Qc
Helper I
3 years ago

No problem, when you can, you are right preferably to have them decimal/integer and yes the numbers are separated by spaces as shown in the screenshot

thank youuu tamer

Amal

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Amy_Qc 
Hi Amal,

Please refer to attached sample file with the solution. Please let me know if you face any trouble implementing the same on your actual data.

Output = 
VAR String = 'Table'[Input]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 0, 9, 1 )
VAR T2 = GENERATESERIES ( 1, Length, 1 )
VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) )
VAR T4 = 
    FILTER ( 
        T3, 
        VAR T5 = FILTER ( T1, CONTAINSSTRING ( [@Item], [Value] ) )
        VAR Condition1 = NOT ISEMPTY ( T5 )
        VAR Condition2 = NOT ( CONTAINSSTRING ( [@Item], "-" ) )
        VAR Condition3 = NOT ( CONTAINSSTRING ( [@Item], "/" ) )
        RETURN
            Condition1 && Condition2 && Condition3
    )
VAR Digits = CONCATENATEX ( T4, [@Item] )
VAR Factor = 
    SWITCH ( 
        TRUE ( ),
        CONTAINSSTRING ( String, "K$" ), 1000,
        CONTAINSSTRING ( String, "M$" ), 1000000,
        CONTAINSSTRING ( String, "B$" ), 1000000000
    )
RETURN
    VALUE ( Digits ) * Factor

 

  • Amy_Qc's avatar
    Amy_Qc
    Helper I
    3 years ago

    Salam Tamer,

    Thank you very much but i have this error 😞 

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Salam Amal,

    Please search this value in your table and share the line that creates this error as I failed to simulate it.

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Amal,

    Looks like a date which the code has converted to this shape somehow. Unless I see how it originally looks like I won't be able to solve the problem.

  • Amy_Qc's avatar
    Amy_Qc
    Helper I
    3 years ago

    I don't find this value!