Forum Discussion
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.
5 Replies
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()
)Thank you, bhanu_gautam, for your response.
Hi Ankurdatascienc,
We would like to check if the solution provided by bhanu_gautam has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.
Thank you.
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,
We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.
If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.
Thank you.Hi Ankurdatascienc,
We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.
If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.
Thank you.
