Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamically remove string from one column if string is contained in another column

I have a dataset that has a primary code for each transaction and then in a separate column has all related codes for that transaction. Probably easiest to just describe it in an example. So for inst...
Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago

    Hi,

     

    maybe this can help. Refer to your table in Source step.

     

    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

     

Jakinta's avatar
Jakinta
Solution Sage
5 years ago

Hi,

 

maybe this can help. Refer to your table in Source step.

 

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

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Awesome, that worked! Thanks a lot for the help!