let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzDUMzIwMlHSUTK0jExLKUoxKXIuqygtS1EwCC7wNfZSitUhRl0WkepKiFNn4kykeVD3mRNSp0KkOkMi1RFrLyRcjAz0DIyIMI+wOqB5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, GUID = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Date", type date}, {"GUID", type text}},"de"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"GUID"}, {{"maxd", each List.Max([Date]), type nullable date}, {"mind", each List.Min([Date]), type nullable date}, {"ct", each Table.RowCount(_), Int64.Type}, {"rows", each _, type table [Date=nullable date, GUID=nullable text, Index=number]}}),
#"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"Date", "Index"}, {"Date", "Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded rows",{"Date", "GUID", "maxd", "mind", "ct", "Index"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Duplicates", (k)=> if Table.RowCount(Table.SelectRows(#"Sorted Rows",each [Date]=k[Date] and [GUID]=k[GUID]))>1 then "Duplicate" else ""),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "New/Old", each if [ct] = 1 or [Date]=[maxd] then "New" else "Old"),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "GUID", "Duplicates", "New/Old"})
in
#"Removed Other Columns"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
