I am seeking for small advise if there is another better way to achieve my results.
unique test.xlsx (202.8 KB)
The table in Columns A-C represents the data that end of results should be a unique list with the max date for each code. This could be done by using excel formula such as MAXIFS, Column I to K shows correct results, but I wanted to build this more dynamic using power query.
I imported the table to power query and using DAX formula I calculated the max date for each unique code =
MAX ( Table1[Date] ),
ALLEXCEPT ( Table1, Table1[Code], Table1[Status] )
Then using power query I created another table where I removed duplicates and column with dates. Then using Dax I added a column with a max date for unique transaction/code:
The result is shown in column M to O.
I just wonder is there any quicker way to do it? Could I achieve the same results using M-Coding?
I will be grateful for you help!