Forum Discussion
davidleal
3 years agoFrequent Visitor
Replace duplicated values with null in the same column per a given list of columns
I found several solutions for a similar problem, but it creates an additional column or does find and replace value which is not what I need. Let's say I have a Name column with the following values:...
- 3 years ago
Hi davidleal ,
utilizing on what you have already, I would suggest the following approach:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W8kvMTTVU0lFyziypBNGGBkqxOpjCRghhI6gwiDbGLmyCKWwMpE2BwrEA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, City = _t, Amount = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Name", type text}, {"City", type text}, {"Amount", Int64.Type}} ), ListOfColumns = {"Name", "City"}, RemainingColumns = List.Difference(Table.ColumnNames(#"Changed Type"), ListOfColumns), RemoveDups = (lst as list) => List.Accumulate(lst, {}, (x, y) => x & {if List.Contains(x, y) then null else y}), replaceValues = List.Transform(ListOfColumns, each RemoveDups(Table.Column(#"Changed Type", _))), Custom1 = Table.FromColumns( replaceValues & Table.ToColumns(Table.SelectColumns(#"Changed Type", RemainingColumns)), ListOfColumns & RemainingColumns ) in Custom1
ImkeF
3 years agoCommunity Champion
Hi davidleal ,
utilizing on what you have already, I would suggest the following approach:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45W8kvMTTVU0lFyziypBNGGBkqxOpjCRghhI6gwiDbGLmyCKWwMpE2BwrEA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Name = _t, City = _t, Amount = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Name", type text}, {"City", type text}, {"Amount", Int64.Type}}
),
ListOfColumns = {"Name", "City"},
RemainingColumns = List.Difference(Table.ColumnNames(#"Changed Type"), ListOfColumns),
RemoveDups = (lst as list) =>
List.Accumulate(lst, {}, (x, y) => x & {if List.Contains(x, y) then null else y}),
replaceValues = List.Transform(ListOfColumns, each RemoveDups(Table.Column(#"Changed Type", _))),
Custom1 = Table.FromColumns(
replaceValues & Table.ToColumns(Table.SelectColumns(#"Changed Type", RemainingColumns)),
ListOfColumns & RemainingColumns
)
in
Custom1