Forum Discussion
Solved
Extracting Certain Characters from Text Column
I have a column where data is in Format of Designation.Function.Skill.Domain and I want to extract only SKill.Domain. Please see the below photo and let me know what is the mistake I am doing. ...
Ankurdatascienc , Try using
DAX
Extract_Skill_Domain =
VAR FirstDot = SEARCH(".", 'Workday Report'[Job Name], 1, -1)
VAR SecondDot = SEARCH(".", 'Workday Report'[Job Name], FirstDot + 1, -1)
VAR ThirdDot = SEARCH(".", 'Workday Report'[Job Name], SecondDot + 1, -1)
RETURN
IF(
FirstDot > 0 && SecondDot > 0 && ThirdDot > 0,
MID('Workday Report'[Job Name], SecondDot + 1, LEN('Workday Report'[Job Name]) - SecondDot),
BLANK()
)Hi @Ankurdatascienc
Is Power Query an option?
If so, you could:
- Duplicate the [Job Name] column.
- Split by delimiter
- “.” As delimiter
- Starting from right
-
- That should give you your [Domain] column.
- Using the “left part” of the previous step, repeat the process.
- That should give you your [Skill] column.
- Delete the resulting “left part” and concatenate and/or rename the new columns accordingly.
I hope this helps.
Hi @Ankurdatascienc
Is Power Query an option?
If so, you could:
- Duplicate the [Job Name] column.
- Split by delimiter
- “.” As delimiter
- Starting from right
-
- That should give you your [Domain] column.
- Using the “left part” of the previous step, repeat the process.
- That should give you your [Skill] column.
- Delete the resulting “left part” and concatenate and/or rename the new columns accordingly.
I hope this helps.