Split Number,StartDate and EndDate from string

[jaypatel]

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

[v-kelly-msft · Accepted solution]

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!

[harshnathani]

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)

[jaypatel]

@harshnathani 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?

[harshnathani]

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)

[jaypatel]

@harshnathani 

I have tried your formulas. But I only get the result from the First Value1 formula. Other 2 formulas are not responding as I expected. Could you please me.

Regards,

Jay

[harshnathani]

Hi @jaypatel ,

Can you share some sample data in text format.

Regards,

Harsh Nathani

[harshnathani]

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)

[amitchandak]

@jaypatel , Try like

number= left([column],4)

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

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

[jaypatel]

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

@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