Extracting specific dates from text column

[blackhall8]

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 Column	ZP Date (desired)	Pre-assessment Date (desired)
02/17/2020 - Introduce Yourself, 02/15/2020 - ZP: Lead Form	02/15/2020
01/09/2020 - ZP: Lead Form	01/09/2020
12/30/2019 - ZP: Lead Form	12/30/2019
12/15/2019 - Introduce Yourself, 12/15/2019 - ZP: Lead Form	12/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

[tkirilov]

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

[Anonymous]

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.

[blackhall8]

@Anonymous , 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

[Anonymous · 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.