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"})
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?
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"
4 Replies
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