Forum Discussion

jaypatel's avatar
jaypatel
Helper III
5 years ago
Solved

Split Number,StartDate and EndDate from string

Hi All,

I have one string column which is the concatenation of the Number, StartDate and EndDate. I want to extract number, StartDate and EndDate from the string. 
Can Anyone help me out for this?

Below is my string: 

Exmple : 1234 (01-03-2022 - 31-03-2022)

I have extracted the number from the string. but have some difficulties to extract StartDate and EndDate from the string. I have used below DAX to extract number. I follow this link.

 

ExtString = LEFT(SUBSTITUTE(Table[Name]," ","-"),SEARCH("-",SUBSTITUTE(Table[Name]," ","-"))-1)

 

Regards,

Jay

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi jaypatel ，

     

    Create 3 calculated columns as below:

    First Value1 = 
var _search1=SEARCH("(",'Table'[Text],1,0)
Return
LEFT('Table'[Text],_search1-1)
    Start date = 
var _search1=SEARCH("(",'Table'[Text],1,0)
Return
MID('Table'[Text],_search1+1,10)
    End date = 
LEFT(RIGHT('Table'[Text],11),10)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jaypatel , Try like

     

    number= left([column],4)

    Start date = mid([column],7,10)

    End Date = mid([column],20,10)

    • jaypatel's avatar
      jaypatel
      Helper III
      5 years ago

      amitchandak Thanks for the quick reply. But my first numbers don't have the same length. It may vary like 1,12,123,1234... etc etc. 

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        jaypatel , try like

         

        left(Table[Col], SEARCH("(",Table[Col],1,0) -2)

        Start date = mid([column],SEARCH("(",Table[Col],1,0)+1,10)

        End Date = mid([column],SEARCH("(",Table[Col],1,0)+14,10)

         

        Minor adjustment need in number I added

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi jaypatel ,

     

    You should try doing this in Power Query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHQMDDUNTDWNTIwMlLQVTCGczSVYnXASgipoMyEWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Text], "("), type text),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text Between Delimiters", each Text.BetweenDelimiters([Text], "(", "-", 0, 2), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([Text], "-", ")", 2, 0), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters1",{{"Text Before Delimiter", "First Value"}, {"Text Between Delimiters", "Start Date"}, {"Text Between Delimiters.1", "End Date"}})
in
    #"Renamed Columns"

     

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi jaypatel ,

       

      In PQ follow these steps.

       

       

       

      Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • jaypatel's avatar
      jaypatel
      Helper III
      5 years ago

      Anonymous I had given a shot in Power Query using a Text function. But is it possible to create a measure to achieve the same result?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi jaypatel ,

         

        I will not suggest you to do this in DAX.

         

        However, these are the formulae you can try

         

         

        First Value1 = 

var _a = SUBSTITUTE('Table'[Text], "-","|",3)
var _b = SUBSTITUTE(_a,"(","|")
var _c = SUBSTITUTE(_b,")","")
RETURN
TRIM(PATHITEM(_c,1))

         

         

         

        StDate = 

var _a = SUBSTITUTE('Table'[Text], "-","|",3)
var _b = SUBSTITUTE(_a,"(","|")
var _c = SUBSTITUTE(_b,")","")
RETURN
TRIM(PATHITEM(_c,2))

         

         

         

        StDate = 

var _a = SUBSTITUTE('Table'[Text], "-","|",3)
var _b = SUBSTITUTE(_a,"(","|")
var _c = SUBSTITUTE(_b,")","")
RETURN
TRIM(PATHITEM(_c,3))

         

         

         

         

        Regards,
        Harsh Nathani
        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi jaypatel ，

     

    Create 3 calculated columns as below:

    First Value1 = 
var _search1=SEARCH("(",'Table'[Text],1,0)
Return
LEFT('Table'[Text],_search1-1)
    Start date = 
var _search1=SEARCH("(",'Table'[Text],1,0)
Return
MID('Table'[Text],_search1+1,10)
    End date = 
LEFT(RIGHT('Table'[Text],11),10)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!