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 into seperate collum.

 

For example we have: 000235123 Example text between numbers 234234424

The goal is that we need to select only text between numbers from this collum and insert into seperate one. Result should be: Example text between numbers

 

We cannot do this in power querry, because increment update will not work, so we need to use some kind of dax function.   

 

Thank you in advance!

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

     

23 Replies

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

     

    • DimaMD's avatar
      DimaMD
      Solution Sage
      4 years ago

      tamerj1 You are incredible, I would shake your hand. Where are you from?
      Greetings from Ukraine

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

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