Forum Discussion

davidleal's avatar
davidleal
Frequent Visitor
3 years ago
Solved

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:...
  • ImkeF's avatar
    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