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

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.