Power Query - remove duplicates error/bug. Problem with Date/Time column sorting.

Question

Hello,

I have encountered a problem in Excel from Office 365 Business on Windows 10 x64 bit. I have imported an xslx file that contains like 4 columns. One has names, another is a true/false column, 3rd contains email addresses and last has a date/time values. The default order for the date column is from oldest (top) to newest (bottom). I use power query to import data, sort with descending order by the date column and then remove duplicates from email address column. I have noticed strange behavior, where excel will ignore the sorting step and proceed to remove duplicates from email addresses column based on the order that was initially in the import file. Where is my problem? Power Query after sorting with descending order and removing duplicates, leaves rows with older dates for duplicate rows, instead of selecting the newest ones. What I want? I want the power query to use the current state (sorted in the query) of data before removing the duplicates, instead of taking the sort order from the original imported file.

The workaround that I have created is that I do import data from external file into the query, sort it with descending order (the column with date/time) then I load the results to a table in the the sheet1, and then from that table I create another query (from range/table) and in that query I am removing duplicates. By this I am able to bypass the bug that I am having, as the imported data is already sorted from newest to oldest results.

I would appreciate a fix that I could sort and remove duplicates in the same query.

Answer 1

Hi,

Given the data shared by Lz, I think the result should be the green table on the right hand side.  My interpretation is that you want to retain the Max date for each name and then extract the Logical and E-mail information against the maximum date for that Name.  You may download my file from here.

Hope this helps.

Answer 2

Bracket Sort() with Table.Buffer()

A debatable bug.

http://www.mediafire.com/file/whrawbglpnyabwu/10_31_18.xlsx/file

Any other function that is designed to react to a preceding sort

will have the same feature (bug?)

Answer 3

Hi #armsdev,

Did you check above replies? Do you need further help?

Regards,

Neha

Answer 4

Hi,

Please share some data and show the expected result.  This should be possible directly in Power Query itself.