Max Date for Unique list

Matty · March 8, 2020, 2:20pm

Hey,

I am seeking for small advise if there is another better way to achieve my results.

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 =
CALCULATE (
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:

=LOOKUPVALUE(Table1[Max Date],Table1[Code],‘Unique’[Code],Table1[Status],‘Unique’[Status])

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!

Melissa · March 8, 2020, 4:51pm

Hi @Matty,

In Power Query you can use Group By to solve this and determine the Max date for each combination.
Results in your file columns: I:K

.
Once that’s done you can Merge these results back into your output table.
Results in your file columns: Q:T

.
Here’s the test file. I hope this is helpful.
Max Date for Unique list.pbix (23.2 KB)

Matty · March 8, 2020, 5:17pm

Thank you @Melissa !