Forum Discussion
Power BI Desktop Extracting Text from a column
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
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 _ResultI'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.
19 Replies
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 _ResultI'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.
I keep getting Token Eof Expected by VAR _17w
Are you adding it as a Custom Column in Power Query or a Calculated Column in DAX? This is DAX.
Here is an example.
Use Split by Delimeter Option in Power Query
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.
could you share a data samole in csv or excel?
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").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.
In Google Drive, instead of selecting Copy Link, select Share. Then click the Copy Link button.
I changed the share setting, it was set to private I beleive, hope it is now accesseble.
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.
