Forum Discussion
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 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.
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
2 Replies
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 & SuffixWhy do you create a variable of the original column instead of just referencing that column in the other variables?
