Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Removing Duplicate with different date format

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"})  ...
Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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 DateSurvey Date - Copy
8/14/2020 10:30:17 AM44057.4376968
8/14/2020 10:47:23 AM44057.4495718
2020-08-14T10:47:22.999000044057.4495717
2020-08-14T10:30:16.999000044057.4376967

 

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Anonymous ,

 

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

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