Forum Discussion
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? ...
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,
KellyDid I answer your question? Mark my post as a solution!
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 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?
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)