Need to validate Duplicates - Then Remove Duplicates

[lardo5150]

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.

[v-yingjl · Accepted solution]

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.

[Anonymous]

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]

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.

[lardo5150]

sorry, I am not understanding.

I am in PowerBi, not Power Apps.

ClearCollect is a PowerApp cmdlet I thought?

[Syndicate_Admin]

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.