Replacing using wildcard

[LMSReportsHelp]

I have been reading posts on here and trying to recreate solutions, but none of them are working and I have no idea why.  I have a list of information which I want to use "Transform Data" to extract the unique information into a new column and then convert into a standard format to show more easily in a table/report.

The data looks something like this:

CLST_A1 Leadership Summit

CLST_A2 Leadership Summit

CLST_A3 Leadership Summit

CLST_B1 Leadership Summit

We currently have items through "F" with schedules through "S".  I already have a new column to extract the group (A1/A2/A3/etc), but now I am trying to replace the A1/A2/A3 to just be "101" since that's the series. In the report, that is what I need to summarize by.  So I would like to be able to create a simple replacement of "CLST_[wildcard/*/%/?]1 Leadership Summit = CLST_101 Leadership Summit" - rather than 60+ and having to edit every time a new group is identified/added. 

Please explain to me like the DAX/Power BI newbie I am how to make this work.

[Sahir_Maharaj · Accepted solution]

Hello @LMSReportsHelp,

Can you please try creating this calculated column:

Transformed Column = 
VAR OriginalText = YourTable[ColumnName]
VAR Prefix = LEFT(OriginalText, 5)
VAR SuffixIndex = SEARCH(" Leadership Summit", OriginalText, 1, LEN(OriginalText))
VAR Suffix = MID(OriginalText, SuffixIndex, LEN(OriginalText) - SuffixIndex + 1)
VAR GroupCode = MID(OriginalText, 6, SuffixIndex - 6) -- Extract the group part like A1, A2, B1, etc.
VAR NewGroupCode = IF(RIGHT(GroupCode, 1) = "1", "101", GroupCode)
RETURN 
Prefix & NewGroupCode & Suffix

[LMSReportsHelp]

Why do you create a variable of the original column instead of just referencing that column in the other variables?