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
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

Bonjour,

Si je veux le contraire, extraire les chiffres svp?

If I want the opposite, extract the digits please?

Thanks in advance

Meci d'avance

 

Amal

  • 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)?