Search code examples
powerbim

How to remove the part of text in one column that matches the value of another column?


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:

email 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?


Solution

  • I found this answer and their solution solved my problem:

    https://community.powerbi.com/t5/Power-Query/Dynamically-remove-string-from-one-column-if-string-is-contained/td-p/1809532

    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