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.

ThxAlot's avatar
ThxAlot
Icon for Super User rankSuper User
1 year ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvDx9dMFEfq+QU4hwbqGJiZG5kb6Pn4oHGdXH11DSxMzS30/J0//kHg3FxddA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
    Extracted = Table.AddColumn(Source, "Extracted", each let str=[String], nr=List.Select(Text.SplitAny(str, Text.Remove(str, {"0".."9"})), each _<>"") in (nr{0}? ?? "") & " - " & (nr{2}? ?? ""))
in
    Extracted