Merge query is acting weird

[GuilleATT]

Hi,

I have a Sales table with closed dates between other variables and I'm trying to create a new column in Power Query to indicate if the Sales is the first time that happens for an AccountID. 

This way, I duplicated my Sales table, reduce it only to be AccountID and ClosedDate, ordering it ascending and removing duplicates on AcccountID column to obtain the first time a Sales happen to each AccountID.

Then on the original sales table, which is filtered now only for 2020, I merge it with the duplicated one to do what an Excel is a simple VLoopUk...

What my surprise is when expanding the columns of the merged table the dates retrieved aren't correct and even some dates retrieved are no longer part of the duplicated table since I removed duplicates...

Appreciate any help on this since it's driving me crazy.

Best,

Guillermo

[Greg_Deckler]

@GuilleATT - Can you post sample data or a link to your PBIX? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[ImkeF · Accepted solution]

Hi @GuilleATT ,

the behaviour you're seeing would happen if you don't use a Table.Buffer when sorting before removing duplicates: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i...

[GuilleATT]

Awesome, that worked, thank you so much!