Forum Discussion

blackhall8's avatar
blackhall8
Frequent Visitor
6 years ago
Solved

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
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      blackhall8
      Frequent Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.

  • tkirilov's avatar
    tkirilov
    Resolver I
    6 years ago

    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