Forum Discussion
Solved
Replacing using wildcard
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 ...
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
Sahir_Maharaj
Super User
Super UserHello 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
Why do you create a variable of the original column instead of just referencing that column in the other variables?
