Remove repeated text within a row

[AmyBeth]

Good morning,

How can I remove repeating values across columns within a row?  I don't have a fixed delimiter to split the cells.  Is there any logic that can look within the row and remove contents that are in column A from Column B for example?   Unfortunately my data is confidential but  tried to create an example using common data below.  I appreciate any help you can offer!  

Thanks!

Amy

Column A	Column B	Column C	Column D
Amy Beth	Amy Beth Smith	Amy Beth Smith Ms	Amy Beth Smith MS and Family
Ishaan	Ishaan Jaglan	Ishaan Jaglan II	Ishaan Jaglan II with Guest
DESIRED DATASET
Amy Beth	Smith	Ms	and Family
Ishaan	Jaglan	II	with Guest

[ImkeF · Accepted solution]

Hi @AmyBeth 

you can strip out the occurrences of the strings from "ColumnA" from other columns like so:

Text.Replace([ColumnName], [Column A], "")

If you have many columns, unpivoting and pivoting back might be the most efficient solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsytVHBKLclQ0oEzFYJzM7EIKPgWYxELVkjMS1FwS8zNzKlUitWJVvIszkhMzAOqhDAUvBLTczD5Cp6eWIQUykFmupemFpcoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", type text}, {"Column D", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column A"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Replace([Value], [Column A], "")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Custom", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Attribute]), "Attribute", "Custom")
in
    #"Pivoted Column"