Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Get value from text after delimiter

Hi, I wanna extract first number 144272(after second -) and third number 19469(after fourth -). Desired output is 114272-19469   Delimiter structure will be same but number string length are dyna...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    Hi Anonymous,

    Here is the DAX measure I used, which produced the desired output. Please review the attached PBIX file for your reference.

    ExtractedCode = 
VAR FullText = [Column1]
VAR FirstSlash = SEARCH("/", FullText, 1)
VAR SecondSlash = SEARCH("/", FullText, FirstSlash + 1)
VAR ThirdSlash = SEARCH("/", FullText, SecondSlash + 1)

VAR Text1 = MID(FullText, FirstSlash + 1, SecondSlash - FirstSlash - 1)
VAR Text2 = MID(FullText, SecondSlash + 1, ThirdSlash - SecondSlash - 1)

VAR Digits = "0123456789"

VAR OnlyNums1 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text1)), 
        "Char", MID(Text1, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

VAR OnlyNums2 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text2)), 
        "Char", MID(Text2, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

RETURN OnlyNums1 & "-" & OnlyNums2

     

    If this post helps, then please consider Accept it as a solution to help the other members find it more quickly.

     

    Thank you.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi Anonymous 

 

Try this as a custom column in the query editor

let 
col = [Column1],
numbers = {"0".."9"}, 
text1 =  Text.Select( Text.BetweenDelimiters(col, "/", "/"), numbers),
text2 =  Text.Select( Text.BetweenDelimiters(col, "/", "/", 2, 0),numbers)
in
text1 & "-" & text2

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks this one seems easiest, can we do that in dax? 

    • v-saisrao-msft's avatar
      v-saisrao-msft
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Anonymous,

      Here is the DAX measure I used, which produced the desired output. Please review the attached PBIX file for your reference.

      ExtractedCode = 
VAR FullText = [Column1]
VAR FirstSlash = SEARCH("/", FullText, 1)
VAR SecondSlash = SEARCH("/", FullText, FirstSlash + 1)
VAR ThirdSlash = SEARCH("/", FullText, SecondSlash + 1)

VAR Text1 = MID(FullText, FirstSlash + 1, SecondSlash - FirstSlash - 1)
VAR Text2 = MID(FullText, SecondSlash + 1, ThirdSlash - SecondSlash - 1)

VAR Digits = "0123456789"

VAR OnlyNums1 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text1)), 
        "Char", MID(Text1, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

VAR OnlyNums2 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text2)), 
        "Char", MID(Text2, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

RETURN OnlyNums1 & "-" & OnlyNums2

       

      If this post helps, then please consider Accept it as a solution to help the other members find it more quickly.

       

      Thank you.

      • HimanshuS-msft's avatar
        HimanshuS-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        1 year ago

        This was helpful.