Forum Discussion
Solved
Removing Duplicate Strings from cells in a column
Hi, Is there a way to remove duplicate strings from all cells in a column? I have a table with a column something like below. Resources UserEmail SPN AAD UserEmail SPN AAD UserEmail ...
You can use the Table.TransformColumns function.
See the code below for an example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLXLNTczMUQgO8FNwdHRRwBBRitVBUofKI1bXYFQXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resources = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Resources", type text}}), #"Remove Duplicate Phrases" = Table.TransformColumns(#"Changed Type",{"Resources", each Text.Combine( List.Distinct( Text.Split(_," ") ), " ")}) in #"Remove Duplicate Phrases"or, since you wrote you have multiple tables, you can write it as a separate function, where you call it by specifying the Table and Column to process:
Add as blank query and rename eg: fnDeDupPhrases
(tbl as table, col as text)=> Table.TransformColumns(tbl,{col, each Text.Combine( List.Distinct( Text.Split(_," ") ), " "), type text})Then use like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLXLNTczMUQgO8FNwdHRRwBBRitVBUofKI1bXYFQXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resources = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Resources", type text}}), #"Remove Duplicate Phrases" = fnDeDupPhrases(#"Changed Type","Resources") in #"Remove Duplicate Phrases"
Hi swalford
As long as your actual date is as clean as your example (meaning, every word is separated by a single space) You can achieve this by splitting the column by delimiter, unpivoting, removing duplicates, and then grouping back again with concatenation (if needed).
Please see my sample Power Query based on your data (I extended it to show the behaviour when your data is not only "Yes". If the result is incorrect please specify what the intended outcome is for those cases)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk4tVorViVaKTC1WgGIYH10cXR5dHBsmxQyCav3ywUw4pYDMUoCogGuAMWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Resources", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Resources"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Resources", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Resources.1", "Resources.2", "Resources.3", "Resources.4", "Resources.5", "Resources.6", "Resources.7", "Resources.8", "Resources.9", "Resources.10"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resources.1", type text}, {"Resources.2", type text}, {"Resources.3", type text}, {"Resources.4", type text}, {"Resources.5", type text}, {"Resources.6", type text}, {"Resources.7", type text}, {"Resources.8", type text}, {"Resources.9", type text}, {"Resources.10", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Index"}, {{"Resources", each Text.Combine([Value]," "), type text}})
in
#"Grouped Rows"
The Group By text concatenation trick is from this blog post by gorilla.bi: https://gorilla.bi/power-query/group-by-to-concatenate-text/
Let me know if you have any questions!
swalford See below the code for your updated sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk4tVorViVYKLU4tcs1NzMxRCA7wU3B0dFHAEEFVR56uwaguFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Resources", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Resources"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Resources", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Resources.1", "Resources.2", "Resources.3", "Resources.4", "Resources.5", "Resources.6", "Resources.7", "Resources.8", "Resources.9", "Resources.10"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resources.1", type text}, {"Resources.2", type text}, {"Resources.3", type text}, {"Resources.4", type text}, {"Resources.5", type text}, {"Resources.6", type text}, {"Resources.7", type text}, {"Resources.8", type text}, {"Resources.9", type text}, {"Resources.10", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Index"}, {{"Resources", each Text.Combine([Value]," "), type text}})
in
#"Grouped Rows"