Forum Discussion
Merge query is acting weird
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
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-in-Power/ba-p/810390
3 Replies
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.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-in-Power/ba-p/810390
Awesome, that worked, thank you so much!
