Forum Discussion
Remove duplicate rows from a calculate table
Hi!
Someone helps me to remove duplicate rows from a calculate Table and take the max value of "Delivery date" for each Order-Line ;
In my case, this is the calculate table :
| Order No | Line No | Delivery Date |
| AAAA | 1 | 06/20/2018 |
| AAAA | 2 | 06/20/2018 |
| AAAA | 2 | 06/20/2018 |
| AAAB | 1 | 06/22/2018 |
| AAAC | 1 | 06/20/2018 |
| AAAA | 3 | 06/20/2018 |
| AAAB | 1 | 06/15/2018 |
| AAAB | 1 | 06/20/2018 |
| AAAC | 3 | 06/20/2018 |
and the result that i'm looking for is below :
| Order No | Line No | Delivery Date |
| AAAA | 1 | 06/20/2018 |
| AAAA | 2 | 06/20/2018 |
| AAAB | 1 | 06/22/2018 |
| AAAC | 1 | 06/20/2018 |
| AAAA | 3 | 06/20/2018 |
| AAAC | 3 | 06/20/2018 |
Thank you
Unique = SUMMARIZE ( Orders, Orders[Order], Orders[Line], "Delivery Date", MAX ( Orders[DelDate] ) )
Hope this helps,
David
6 Replies
Unique = SUMMARIZE ( Orders, Orders[Order], Orders[Line], "Delivery Date", MAX ( Orders[DelDate] ) )
Hope this helps,
David
Thank you lot dedelman_clng
My situation is more complicated then that, cuz my table is not a data but a GENERATE (Table1;Table2).
I'll try to post the complete case, hoping that someone will help me.Yasser92 you should be able to do a calculated table (SUMMARIZE retruns a table) against other calculated tables.
Hi Yasser92,
What I would add is there's a simpler way in power bi to achieve this. Click Editor Queries-> click on column [Order No], [Line No] and [Delivery Date]-> click Remove Rows-> select Remove duplicates.
Before:
After:
Hope it's helpful to you.
Jimmy Tao
Yasser92 was asking to remove duplicates for Order-Line and displaying the Max delivery date. Query Editor would not be able to provide that (note that you have 3 rows for Order AAAB, Line 1)
