Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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-i...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Awesome, that worked, thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.