Forum Discussion
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"}) ...
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"
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"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 Anonymous - Change your rounding to 5 decimal places versus 7
Hi Anonymous ,
You need to choose the column "ANI" and "Survey Date - Copy".
= Table.Distinct(#"Changed Type", {"ANI", "Survey Date - Copy"})
