Forum Discussion
Remove repeated text within a row
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 |
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"
1 Reply
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"