cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blackhall8
Frequent Visitor

Extracting specific dates from text column

I can't seem to figure out how to extract a specific date from a text column in my data table. As you can see below, I have a "Source Column" that includes a comma seperated list of interactions. What I would like to do is create two custom columns that include the the date associated with "ZP: Lead Form" and "Pre-assessment". I've tried using the Text Between Delimiters in Power Query, but that wouldn't capture the rows that only had a single interaction (i.e. 01/09/2020 - ZP: Lead Form). Can someone let me know if it's possible to achieve what I'm looking for in the two desired results columns?

 

Source ColumnZP Date (desired)Pre-assessment Date (desired)
02/17/2020 - Introduce Yourself, 02/15/2020 - ZP: Lead Form02/15/2020 
01/09/2020 - ZP: Lead Form01/09/2020 
12/30/2019 - ZP: Lead Form12/30/2019 
12/15/2019 - Introduce Yourself, 12/15/2019 - ZP: Lead Form12/15/2019 
11/13/2019 - Introduce Yourself  
08/21/2016 - Schedule Visit, 08/21/2016 - Pre-assessment, 08/21/2016 - Introduce Yourself 08/21/2016
08/15/2016 - Pre-assessment
 08/15/2016
12/22/2017 - Start Application, 12/22/2017 - Pre-assessment, 12/22/2017 - Introduce Yourself 12/22/2017
1 ACCEPTED SOLUTION

if Text.Contains([Sources], "ZP")

then

Text.Middle([Sources],Text.PositionOf([Sources], "ZP") -13,11)

else

null

 

Text.Middle goes into sources, starting from the position of "ZP" minus 13 and extracts 11 charachters.

 

Adjust 13 and 11 if needed.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
tkirilov
Resolver I
Resolver I

Hi @blackhall8 ,

You could achieve your desired result by splitting the column by a custom delimiter in the Query Editor. You might need to do that twice, once where your custom delimiter is " - ZP:" and second, where the custom delimiter is " - Pre-". Hope this helps.

Best,

Tom

adetogni
Solution Sage
Solution Sage

I would add a custom formula in powerquery using a if

 

ZP Date=

if (string contains ZP) then

find the position of "ZP", go back 13 chars and get 10 chars, trim it -> return this value
else

return emtpy

 

PreAssessment Date=

if (string contains Pre-Assessment) then

find the position of "ZP", go back 13 chars and get 10 chars, trim it -> return this value
else

return emtpy


etc


TextBetweenDelimiters works fine when your data is very well structured.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@adetogni , thank you. I think your solution will work, but I'm having trouble with the "get 10 chars, trim it -> return this value.." portion. What do I need to add from here?

 

ZPDate:

if Text.Contains([Sources], "ZP") then Text.PositionOf([Sources], "ZP", -13) else null

 

Thanks,
Brian

if Text.Contains([Sources], "ZP")

then

Text.Middle([Sources],Text.PositionOf([Sources], "ZP") -13,11)

else

null

 

Text.Middle goes into sources, starting from the position of "ZP" minus 13 and extracts 11 charachters.

 

Adjust 13 and 11 if needed.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors