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 | |
---|---|
george@hotmail.com | 79fsdfv8v2657f842356j0 |
784ret3956w35497er834r62mary@hotmail.com | 784ret3956w35497er834r62 |
34gfng6w5h6pauly@outlook.com | 34gfng6w5h6 |
Resulting column:
george@hotmail.com
mary@hotmail.com
pauly@outlook.com
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