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 )

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Amy_Qc 

please provide more details perhaps with example. 

Amy_Qc's avatar
Amy_Qc
Helper I
3 years ago

 

want extract just numbers with $, thank you tamer in advance 🙂

amal

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Amy_Qc 

    It's too late now, I'll work on tomorrow morning. However, I have some questions?

    do the numbers come with spaces in between as shown in screenshot?

    would you like the output to be of decimal/integer data type (can be used later to perform further calculations) or would you like to have them as is (string type)?

    • 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