Forum Discussion

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

Need Help

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!
Split Number,StartDate and EndDate from string.pbix25 KB

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"