Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |