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


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