Forum Discussion
Create new table from rows in another table, where distinct column A value is the earliest of group
I have a table similar to below:
A | B | C | D
Apple | Something | Else | Jan 1, 2020
Banana | Something | Else | Jan 3, 2020
Apple | Something | Else | Dec 28, 2019
Pear | Something | Else | Dec 29, 2019
Apple | Something | Else | Jan 7, 2020
Banana | Something | Else | Jan 24, 2020
Apple | Something | Else | Dec 21, 2019
Pineapple | Something | Else | Dec 15, 2019
Apple | Something | Else | Jan 13, 2020
Apple | Something | Else | Jan 21, 2020
I have already sorted out by column A and then column "D"
A | B | C | D
Apple | Something | Else | Dec 21, 2019
Apple | Something | Else | Dec 28, 2019
Apple | Something | Else | Jan 1, 2020
Apple | Something | Else | Jan 7, 2020
Apple | Something | Else | Jan 13, 2020
Apple | Something | Else | Jan 21, 2020
Banana | Something | Else | Jan 3, 2020
Banana | Something | Else | Jan 24, 2020
Pear | Something | Else | Dec 29, 2019
Pineapple | Something | Else | Dec 15, 2019
But if I do a Table.Distinct for column A hoping it removed the second and after row, having been placed in order by date already too, it does not do that, but returns:
A | B | C | D
Apple | Something | Else | Jan 7, 2020
Banana | Something | Else | Jan 24, 2020
Pear | Something | Else | Dec 29, 2019
Pineapple | Something | Else | Dec 15, 2019
Not sure how else to accomplish this...
Was able to get what I needed by creating new table from the source table with only the necessary columns; I need the original table for other visuals. Then I selected the two columns that mattered and removed duplicates that way, that seemed to get the topmost value in the sorted by Date column.
2 Replies
- amitchandak
Super User
I am not sure of logic of 7 jan for Apple. But if it 21 jan
The try
New table = Summarize(Table,Table[A],Table[B],Table[C],"D",max(Table[D]))Hope this will help.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin Was able to get what I needed by creating new table from the source table with only the necessary columns; I need the original table for other visuals. Then I selected the two columns that mattered and removed duplicates that way, that seemed to get the topmost value in the sorted by Date column.
