Keep the most recent assigned salesperson

[porter444]

I have a table that contains sales detail transaction lines. I need to build a list of the current assigned salesperson for each account. To do this, my thought was to eliminate all the columns and just keep the following:

• Invoice Date
• Assigned Salesperson
• Account Number
• Account Name

I removed all of the duplicates, and now have a list of each invoice date a customer transaction occurred. What I want to end up with is just the most recent transaction for each customer. That will give me the assigned salesperson based on the most recent transaction.

Suggestions?

 

[Dan Bliss]

Perhaps I don't understand, but why can't you just Sort by Invoice date descending, then remove customer duplicates.
Steps in this order will remove each customers' earlier dates leaving just the most recent.

Advanced editor looks like this:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Date", type date}, {"Account Name", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Invoice Date", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Account Name"})
in
#"Removed Duplicates"