Power BI Desktop Extracting Text from a column

[fereshtehaghaei]

I am fairly new to Power BI and I need to extract the following text from a column field/column in Power BI 1st 7 Weeks 2nd 7 Weeks 1st 8 Weeks 2nd 8 Weeks and for those titles that don't have any weeks indicated, I need to set the extracted value to Full 16 Weeks (which doesn't exist in title field and I need to give that value)
I try to use split delimiter function but the value that I am extracting is not consistently appearing in the same order and for some title the placement is different. I would appreciate any guidance and help.

Title formats as shown below

NUR-4450-Z2-Holistic Nursing Care of Women and Families-Fall 2022-Clinical 06 - 1st 8 Weeks - Tuesday - Motl

NUR-3480-Z1-Holistic Nursing Care Adults and Families I-Fall 2022-Clinical 02 - Wednesday - Prov Park - Schaw

NUR-4410-Z1-Holistic Nursing Care of Children and Families-Fall 2022-Clinical 08 - 2nd 8 Weeks - Tuesday - Ratl

NUR 3460-Z2 Holistic Mental Health Nursing Care-WIN23-Clinical 05 - 2nd 8 Weeks - Monday - Mac

NUR-3970-Z1 Population Focused Care of The Older Adult and Family-SP23-Clinical 01 - 2nd 7 Weeks - Wednesday - HV Pace - Wil

NURS-4200-AN1 Nursing Adults Acute Health - Fa23-NURS-4200-AN1

NURS-3510-AN1 Ns Adlt/Chr Hlth - Su23-NURS-3510-AN1

https://drive.google.com/file/d/1sGshZvTlhbcX7kmEgMk5kctEm20wqewN/view?usp=sharing

[gmsamborn · Accepted solution]

@fereshtehaghaei 

Would a calculated column like this help?

z = 
VAR _17w = IF( CONTAINSSTRING( [Title (Courses)], "1st 7 Weeks"	), "1st 7 Weeks" )
VAR _27w = IF( CONTAINSSTRING( [Title (Courses)], "2nd 7 Weeks" ), "2nd 7 Weeks" )
VAR _18w = IF( CONTAINSSTRING( [Title (Courses)], "1st 8 Weeks" ), "1st 8 Weeks" )
VAR _28w = IF( CONTAINSSTRING( [Title (Courses)], "2nd 8 Weeks" ), "2nd 8 Weeks" )
VAR _Result = COALESCE( _17w, _18w, _27w, _28w, "Full 16 Weeks" )
RETURN
	_Result

I'm sure there is a better way to do it with a list of search-strings but I can't think of one right now.

[fereshtehaghaei]

I keep getting Token Eof Expected by VAR _17w

[gmsamborn]

Are you adding it as a Custom Column in Power Query or a Calculated Column in DAX?  This is DAX.

Here is an example.

fereshtehaghei.pbix

[fereshtehaghaei]

@gmsamborn Thank You! Yes I was adding it as Custom Column and then I tried Advanced editor. I created as DAX and got a calculated column (called z)  with no errors and that did give me the weeks value I was looking for. While I was testing this, I also used conditional column and gave me the same results. 

Do you know if that would make a difference, as far as which approach might be better since this column will be used as a filter on my report page?

[gmsamborn]

@fereshtehaghaei 

I'm guessing that the conditional column would translate to close to the same query.

I'd rather have it a calculated column since it's easier for me to change.  That's just my preference.

[fereshtehaghaei]

I appreciate your time and help very much! I will use the calculated field since as you mentioned it is easier to edit or make any changes later on. If I come across any other solution regarding this text extract I will post my solution here. Thank you for resolving this!

[gmsamborn]

Hi @fereshtehaghaei 

In Google Drive, instead of selecting Copy Link, select Share.  Then click the Copy Link button.

[fereshtehaghaei]

I changed the share setting, it was set to private I beleive, hope it is now accesseble.

[gmsamborn]

Hi @fereshtehaghaei 

Yes.  It is accessible now.

I really don't know how to proceed. 

It seems impossible to do it by separating the different sub-fields but it isn't consistent enought to be able to work with.

Your best is probably a calculated column that searches for the different search strings you have.  As far as I can tell, it would end up being fairly lengthy since the search strings would probably have to be handled separately, one after another.

Maybe someone else can come up with an easier method.

[zenisekd]

Hi, I would suggest you:
1. split the column by "Weeks", then
2. split the first column by the last appearing "- ". The result of the first row should be "1st 8 ". Then I would add the suffix "Weeks". 
3. This will result in "1st 8 Weeks" in the first row and solve also all other rows, which contain the week value.
4. Finally, I would add a conditional column, which in case, the column equals just "Weeks", returns "Full 16 Weeks". In other cases, return the value from the column (which gives for the first row "1st 8 Weeks").

[fereshtehaghaei]

Hi Zenisekd, 

thank you for your tips, unfortunalty I tried and I am not able to get the extract to work in a right way.

[mh2587]

Use Split by Delimeter Option in Power Query

[fereshtehaghaei]

Thanks for taking a look at it, I tried but titles are not consistent through out the # of weeks are not appearing at the same spot through out the data.

[zenisekd]

could you share a data samole in csv or excel?

[fereshtehaghaei]

@zenisekd I used this custom formula in my custom column but for some titles it is extracting somehting different due to title being different for some courses. I am not sure how to tweak this formula to get it working for any format that is the course title is written. Any idea how to edit this to make it work ?

= Table.AddColumn(#"Renamed Columns1", "Weeks", each if Text.BetweenDelimiters([#"title"], " - ", "Weeks") = "" then "All 16 Weeks" else Text.BetweenDelimiters([#"title"], " - ", "Weeks")&" Weeks")

[fereshtehaghaei]

I have a csv file but this platform It's not giving me an option to attach a file?

[gmsamborn]

Hi @fereshtehaghaei 

You have to upload your file to one of OneDrive, GoogleDrive, Dropbox, WeTransfer, etc.
Then create a link to your file and share that link.

[fereshtehaghaei]

I have it uploaded. The first column is what I have as a text format and the last 2 columns is the result I am trying to acheive via extracting from 1st column

https://drive.google.com/file/d/1sGshZvTlhbcX7kmEgMk5kctEm20wqewN/view?usp=sharing

[gmsamborn]

Hi @fereshtehaghaei 

"No Access"

I'm sorry but I use One Drive (and have no problems).  Also, I've never tried any of the others listed above.

I'm sure someone will be along soon.