Forum Discussion

lardo5150's avatar
lardo5150
Microsoft Employee
4 years ago
Solved

Need to validate Duplicates - Then Remove Duplicates

I am pulling data from a table in the dataverse.

 

Due to some issues last week, I have duplicates somewhere in this table.

I need to accomplish two things.

1.  I want to verify the duplicates before I delete them.

Duplicates will be the same items in crb3f_engineer_smpt, crb3f_assignment_time, and crb3f_case_number

I want to view these to see how many there are and if they are really duplicates.

 

2.  If I verify these need to go, I need a way to remove one of the entries, or merge them.

 

There could be a possibility there are triplets.

 

I already have an index column also.

 

Need Help
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi lardo5150 ,

    You can try to use Unpivot and pivot columns feature to remove duplicates values in rows, refer this sample query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSgeJYHQjPCYidwTwnKM8JzIsAsiKBOEopNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns", {"Index", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    For performing this you can create collection  to remove duplicates

    PowerApps Remove Duplicate in a Collection
    for looking duplicates we have to check with the distinct function
    1. ClearCollect(colDistinct,Distinct(colSample,CreatedDate));
    2. Clear the collection before collect. by using function  => Clear(colFinal);
    3. Loop through the Distinct collection and get the first item from the main collection   ForAll(colDistinct,Collect(colFinal,First(Filter(colSample,CreatedDate = Result))
      )
      );  you can try these functions on collection.
  • lardo5150's avatar
    lardo5150
    Microsoft Employee
    4 years ago

    sorry, I am not understanding.

    I am in PowerBi, not Power Apps.

    ClearCollect is a PowerApp cmdlet I thought?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Under the Keep Rows GUI, there is "Keep Duplicates" which will return your duplicates into a separate table. "Remove Duplicates" will remove duplicates.

     

    --Nate

    • lardo5150's avatar
      lardo5150
      Microsoft Employee
      4 years ago

      hmmm, let me try it again.

      I did that but it never created another table.

      Not all columns will have the duplicate.

      I highlighte the three columns that I belive will have the duplicate information.  I then do keep duplicates.

      Got the spinning wheel for a minute or so, then nothing.

  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi lardo5150 ,

    You can try to use Unpivot and pivot columns feature to remove duplicates values in rows, refer this sample query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSgeJYHQjPCYidwTwnKM8JzIsAsiKBOEopNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns", {"Index", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.