I have a table with a column of email addresses and another column of unique IDs. For some reason, some of the values in the email address are prefixed with the unique IDs so I need to do some cleanup of the email address column by removing the unique ID prefix from the email address column. The length of the unique ID can vary, so it's not as straight forward as the only method I know of simply removing the first x amount of characters:
unique ID | |
---|---|
[email protected] | 79fsdfv8v2657f842356j0 |
[email protected] | 784ret3956w35497er834r62 |
[email protected] | 34gfng6w5h6 |
Resulting column:
[email protected]
[email protected]
[email protected]
The solution has to be in m code, not DAX. Any ideas?
I found this answer and their solution solved my problem:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
CLEAN = Table.ReplaceValue(Table.TransformColumns(Table.AddColumn(Source, "All ICD-10 Replaced",
each if Text.Contains([#"All ICD-10"], [#"ICD-10"])
then Text.Replace([#"All ICD-10"], [#"ICD-10"], "")
else [#"All ICD-10"]),
{{"All ICD-10 Replaced", Text.Trim, type text}}),
" "," ",Replacer.ReplaceText,{"All ICD-10 Replaced"})
in
CLEAN