Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Duplicates

Hi i have a dataset with duplicates for multiple values and i want to remove duplicates for only one of the value.

for examples i have column where i have duplicates for 1,2,3 are want to remove duplicates of value 1 only not other values.

 

pl help.

Need Help

5 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Anonymous, you should provide sample data so we can copy/paste and expected result based on sample data. But the idea how to do that could be this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKThkgkRMQYScQYVWUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    FilteredRowsDuplicatesToRemove = Table.SelectRows(ChangedType, each ([Column1] = 1)),
    RemovedDuplicates = Table.Distinct(FilteredRowsDuplicatesToRemove),
    StepBack = ChangedType,
    FilteredRowsDuplicatesToKeep = Table.SelectRows(StepBack, each ([Column1] <> 1)),
    Combined = RemovedDuplicates & FilteredRowsDuplicatesToKeep
in
    Combined
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      what if the values that need to remove are text?

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        It doesn't matter. The logic is the same.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

       

      is there any option if we want to keep the last appearance instead of first appearance of duplicate value?

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Yes, but provide sample data as table so we can copy/paste.