Removing Duplicate with different date format

[Anonymous]

Hello Everyone,

Before anything, here's some of the details you may need to know:

My Steps:

Content of Remove Duplicates1:

= Table.Distinct(#"Appended Query", {"Survey Date"})

My Table after doing Filtered Rows1:

What I wanna have:

What I did is to try and remove the duplicates based on ANI and Survey Date but it would appear that in this particular example the "Remove Duplcates1" didn't push through.

Upon looking at the dates, here's what I found on the following rows:

Row 1: 8/14/2020 9:02:14 PM

Row 2: 2020-08-14T21:02:13.9990000

Now looking at the table, they seem to share the same value but from the backend, they don't.

Can someone help me with fixing the format of my dates into m/d/yyyy h:mm:ss AM/PM? So that the Remove Duplicates function will only return one row?

[Greg_Deckler · Accepted solution]

Having this as text, I did this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3NNE3MjAyULC0MjCyMjRRCPBVitWJVgKJ6RpY6BqahBgZgqWM9SwtLQ2AQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Changed Type1",{{"Column1", each Number.Round(_, 7), type number}}),
    #"Removed Duplicates" = Table.Distinct(#"Rounded Off")
in
    #"Removed Duplicates"

[Anonymous]

@Greg_Deckler 

I tried doing the steps you created with a duplicate column of survey date and here's the result:

Table (after applying the function you created for the survey date - I applied it on a column I duplicated but the problem still persist:

What I'm expecting:

Here's the raw values in table form:

Survey Date	Survey Date - Copy
8/14/2020 10:30:17 AM	44057.4376968
8/14/2020 10:47:23 AM	44057.4495718
2020-08-14T10:47:22.9990000	44057.4495717
2020-08-14T10:30:16.9990000	44057.4376967

[v-xuding-msft]

Hi @Anonymous ,

You need to choose the column "ANI" and "Survey Date - Copy".

= Table.Distinct(#"Changed Type", {"ANI", "Survey Date - Copy"})

[Greg_Deckler]

@Anonymous - Change your rounding to 5 decimal places versus 7